WITH TACKBACKNOS AS
(SELECT MAX (CC) TAKEBACKNO) MAXTAKEBACKNO
The FROM (SELECT arjun AKEDATE AS MAKEDATE,
A.C ERTIFYCODE AS CERTIFYCODE,
A.O PERATOR AS OPERATOR,
A.S TATEFLAG AS STATEFLAG,
A.T AKEBACKNO AS TAKEBACKNO,
MIN (A.S TARTNO) AS STARTNO,
MAX (A.E NDNO) AS ENDNO
The FROM LZCARDTRACKB A
LEFT the JOIN LMCERTIFYDES SS
ON A.C ERTIFYCODE=SS. CERTIFYCODE
LEFT the JOIN LDUSER SA
ON A.O PERATOR=SA. USERCODE
WHERE 1=1
AND A.S TATEFLAG IN
(' 4 ', '5', 6 ', '7', '9', '10', '12' and '13', '14')
AND SA.com code LIKE '86%'
AND SA.COM CODE LIKE '86%'
AND arjun akeDate between the date '2017-11-11' AND the date
'2018-05-18'
GROUP BY arjun AKEDATE,
A.C ERTIFYCODE,
A.O PERATOR,
A.S TATEFLAG,
A.T AKEBACKNO) CC
GROUP BY CC MAKEDATE, CC CERTIFYCODE, CC. STARTNO, CC. ENDNO)
SELECT (SELECT i.com code from lduser I where i.u sercode=A.O PERATOR),
(the select ia. Name
The from ldcom ia
Where ia.com code=
(select code from i.com lduser I where i.u sercode=A.O PERATOR)),
(the SELECT LDCODE. CODENAME
The FROM LDCODE
WHERE LDCODE. CODETYPE='cardbusinesstype'
AND LDCODE. CODE=
(select IC. CERTIFYCLASS2
The from lmcertifydes IC
Where IC. Certifycode=a.c ertifycode)),
A.C ERTIFYCODE AS CERTIFYCODE,
(select IC. CERTIFYNAME
The from lmcertifydes IC
Where IC. Certifycode=a.c ertifycode),
MIN (A.S TARTNO) AS STARTNO,
MAX (A.E NDNO) AS ENDNO,
MAX (A.E NDNO) - MIN (A.S TARTNO) + 1 AS SUMCOUNT,
Arjun AKEDATE AS MAKEDATE,
(the SELECT LDCODE. CODENAME
The FROM LDCODE
WHERE LDCODE. CODETYPE='cardstateflag'
AND LDCODE. CODE=
(SELECT DISTINCT (d.S TATEFLAG)
The FROM LZCARDTRACKB d
Where which AKEBACKNO=A.T AKEBACKNO)) as statefalgcode,
(the SELECT LDCODE. CODENAME
The FROM LDCODE
WHERE LDCODE. CODETYPE='usethechannel'
AND LDCODE. CODE=
(select IC. USETHECHANNEL
The from lmcertifydes IC
Where IC. Certifycode=a.c ertifycode))
A, the FROM LZCARDTRACKB TACKBACKNOS
WHERE 1=1
AND A.T AKEBACKNO=TACKBACKNOS. MAXTAKEBACKNO
GROUP BY arjun AKEDATE, A.C ERTIFYCODE, A.O PERATOR, A.T AKEBACKNO
The ORDER BY arjun AKEDATE, A.C ERTIFYCODE, A.O PERATOR, A.T AKEBACKNO

CodePudding user response:

View its execution plan, the estimate is lack of the necessary indexes

CodePudding user response:

Look this is nothing to optimize the SQL, unless the tables are involved is small, otherwise must be a long time, or just have the patience to wait not to come out of the problem

CodePudding user response:

Personal view
TACKBACKNOS needn't set of two layers, directly take Max line
Like 86% if this field is more than two, certainly can try between 86 and 87

CodePudding user response:

(the SELECT LDCODE. CODENAME
The FROM LDCODE
WHERE LDCODE. CODETYPE='cardstateflag'
AND LDCODE. CODE=
(SELECT DISTINCT (d.S TATEFLAG)
The FROM LZCARDTRACKB d
Where which AKEBACKNO=A.T AKEBACKNO)) as statefalgcode, this step must be slow, you don't write the full amount of query the scalar subquery, write their own an outer join