Question

哪位大侠可以帮我优化下以下SQL语句，这个查询出来有点慢，急。。谢谢！

SELECT Z5.BILLTYPEID,Z5.Num_,Z6.DEFAULTCAPTION,Z6.TFormName_ AS FormName_
FROM (
SELECT Z2.BILLTYPEID,COUNT(Z2.BILLNUMBER) AS Num_ FROM (
SELECT DISTINCT Z1.BILLTYPEID,Z1.BILLNUMBER FROM (
SELECT D.ORDERFORM_MID,D.BILLIT,D.BILLTYPEID,D.BILLNUMBER,D.BILLDATE,D.Flag,D.DeptCode_ FROM WorkFlowLog AS D
WHERE NOT Exists (Select ORDERFORM_MID,BILLIT,BILLTYPEID,BILLNUMBER,BILLDATE,Flag,DeptCode_ FROM WorkFlowLog AS E
WHERE D.ORDERFORM_MID=E.ORDERFORM_MID AND D.BILLTYPEID=E.BILLTYPEID AND D.BILLNUMBER=E.BILLNUMBER AND D.BILLIT<E.BILLIT
AND coalesce(D.DeptCode_,'')=coalesce(E.DeptCode_,'') )
AND coalesce(D.Type_,'')<>'comfirm'
) AS Z1
INNER JOIN ( SELECT * FROM AuditFlow WHERE coalesce(Type_,'') <>'SendMail' AND Trim(STATUS_)='1') AS Z
ON Z1.BILLTYPEID=Z.TB_ AND coalesce(Z1.Flag,0)=coalesce(Z.Level_,0)-1 AND coalesce(Z.DeptCode_,'')=coalesce(Z1.DeptCode_,'') AND coalesce(Z.Type_,'') NOT IN ('SendMail') AND Z.EmployeeID_='8909997'
) AS Z2 Group By Z2.BILLTYPEID
) AS Z5
LEFT OUTER JOIN Tmenulist AS Z6 ON Z5.BILLTYPEID=Z6.FormID
ORDER BY Z5.BILLTYPEID

Answer 1

我觉得很有问题，这个语句，从几个方面来说。
1.首先语句的书写格式上，没有严格规范自己的语法格式，维护起来很难理解；

2.嵌套的查询太多，一般情况嵌套的语句低于2个嵌套；

3.查询语句中尽量避免和减少 select * from这样的语句，建议使用要用到的字段代替*。

提供参考思路：
嵌套的语句可以单独罗列出来，并且使用临时表存储，后面再关联。

还有就是创建索引吧，这样速度快些。