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
(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
TACKBACKNOS没必要套两层,直接取max就行
like 86%的这个字段要是肯定大于两位,可以试试between 86 and 87
FROM LDCODE
WHERE LDCODE.CODETYPE = 'cardstateflag'
AND LDCODE.CODE =
(SELECT DISTINCT (d.STATEFLAG)
FROM LZCARDTRACKB d
where d.TAKEBACKNO = A.TAKEBACKNO)) as statefalgcode, 这一步肯定慢的,你这种全量查询就不要写这种标量子查询了,自己写个外连接