SQL执行效率太差，需要优化一下。希望大神指点一下。

[qq_33709910]

WITH TACKBACKNOS AS
(SELECT MAX(CC.TAKEBACKNO) MAXTAKEBACKNO
FROM (SELECT A.MAKEDATE AS MAKEDATE,
A.CERTIFYCODE AS CERTIFYCODE,
A.OPERATOR AS OPERATOR,
A.STATEFLAG AS STATEFLAG,
A.TAKEBACKNO AS TAKEBACKNO,
MIN(A.STARTNO) AS STARTNO,
MAX(A.ENDNO) AS ENDNO
FROM LZCARDTRACKB A
LEFT JOIN LMCERTIFYDES SS
ON A.CERTIFYCODE = SS.CERTIFYCODE
LEFT JOIN LDUSER SA
ON A.OPERATOR = SA.USERCODE
WHERE 1 = 1
AND A.STATEFLAG IN
('4', '5', '6', '7', '9', '10', '12', '13', '14')
AND SA.comcode LIKE '86%'
AND SA.COMCODE LIKE '86%'
AND a.MakeDate between date '2017-11-11' and date
'2018-05-18'
GROUP BY A.MAKEDATE,
A.CERTIFYCODE,
A.OPERATOR,
A.STATEFLAG,
A.TAKEBACKNO) CC
GROUP BY CC.MAKEDATE, CC.CERTIFYCODE, CC.STARTNO, CC.ENDNO)
SELECT (select i.comcode from lduser i where i.usercode = A.OPERATOR),
(select ia.name
from ldcom ia
where ia.comcode =
(select i.comcode from lduser i where i.usercode = A.OPERATOR)),
(SELECT LDCODE.CODENAME
FROM LDCODE
WHERE LDCODE.CODETYPE = 'cardbusinesstype'
AND LDCODE.CODE =
(select ic.CERTIFYCLASS2
from lmcertifydes ic
where ic.certifycode = a.certifycode)),
A.CERTIFYCODE AS CERTIFYCODE,
(select ic.CERTIFYNAME
from lmcertifydes ic
where ic.certifycode = a.certifycode),
MIN(A.STARTNO) AS STARTNO,
MAX(A.ENDNO) AS ENDNO,
MAX(A.ENDNO) - MIN(A.STARTNO) + 1 AS SUMCOUNT,
A.MAKEDATE AS MAKEDATE,
(SELECT LDCODE.CODENAME
FROM LDCODE
WHERE LDCODE.CODETYPE = 'cardstateflag'
AND LDCODE.CODE =
(SELECT DISTINCT (d.STATEFLAG)
FROM LZCARDTRACKB d
where d.TAKEBACKNO = A.TAKEBACKNO)) as statefalgcode,
(SELECT LDCODE.CODENAME
FROM LDCODE
WHERE LDCODE.CODETYPE = 'usethechannel'
AND LDCODE.CODE =
(select ic.USETHECHANNEL
from lmcertifydes ic
where ic.certifycode = a.certifycode))
FROM LZCARDTRACKB A, TACKBACKNOS
WHERE 1 = 1
AND A.TAKEBACKNO = TACKBACKNOS.MAXTAKEBACKNO
GROUP BY A.MAKEDATE, A.CERTIFYCODE, A.OPERATOR, A.TAKEBACKNO
ORDER BY A.MAKEDATE, A.CERTIFYCODE, A.OPERATOR, A.TAKEBACKNO

[jdsnhan]

查看其执行计划，估计又是缺少必备的索引

[minsic78]

看上去这个SQL是没什么好优化了，除非涉及到的表都比较小，否则必定时间长，只不过是有耐心等出来还是等不出来的问题

[yuyu1980]

个人观点 TACKBACKNOS没必要套两层，直接取max就行 like 86%的这个字段要是肯定大于两位，可以试试between 86 and 87

[「已注销」]

(SELECT LDCODE.CODENAME FROM LDCODE WHERE LDCODE.CODETYPE = 'cardstateflag' AND LDCODE.CODE = (SELECT DISTINCT (d.STATEFLAG) FROM LZCARDTRACKB d where d.TAKEBACKNO = A.TAKEBACKNO)) as statefalgcode, 这一步肯定慢的，你这种全量查询就不要写这种标量子查询了，自己写个外连接