这条sql语句是查询每月最后一天的各项值,现在查询时间为9秒左右,表没有加索引,请朋友们给优化优化到2秒左右
select A.ID,
A.BZ, A.JH, A.JB, A.YT, A.SFXT, A.ZDXYY, A.ZYNR, A.DXFL, A.TSQKMS, A.XXXT, A.SFTSZL,
A.SJRQ, A.WJRQ,A.TCRQ, B.GLYMC, C.DEPTNAME,
datediff(day, SJRQ, WJRQ) DXZTS,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 1 and day(D.RQ) = 31 then D.DYM end) DYM1,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 1 and day(D.RQ) = 31 then D.RY end) RY1,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 1 and day(D.RQ) = 31 then D.RYOU end) RYOU1,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 1 and day(D.RQ) = 31 then D.HS end) HS1,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 1 and day(D.RQ) = 31 then D.RZY end) LZY1,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 2 and day(D.RQ) = 28 then D.DYM end) DYM2,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 2 and day(D.RQ) = 28 then D.RY end) RY2,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 2 and day(D.RQ) = 28 then D.RYOU end) RYOU2,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 2 and day(D.RQ) = 28 then D.HS end) HS2,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 2 and day(D.RQ) = 28 then D.RZY end) LZY2,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 3 and day(D.RQ) = 31 then D.DYM end) DYM3,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 3 and day(D.RQ) = 31 then D.RY end) RY3,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 3 and day(D.RQ) = 31 then D.RYOU end) RYOU3,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 3 and day(D.RQ) = 31 then D.HS end) HS3,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 3 and day(D.RQ) = 31 then D.RZY end) LZY3,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 4 and day(D.RQ) = 30 then D.DYM end) DYM4,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 4 and day(D.RQ) = 30 then D.RY end) RY4,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 4 and day(D.RQ) = 30 then D.RYOU end) RYOU4,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 4 and day(D.RQ) = 30 then D.HS end) HS4,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 4 and day(D.RQ) = 30 then D.RZY end) LZY4,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 5 and day(D.RQ) = 31 then D.DYM end) DYM5,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 5 and day(D.RQ) = 31 then D.RY end) RY5,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 5 and day(D.RQ) = 31 then D.RYOU end) RYOU5,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 5 and day(D.RQ) = 31 then D.HS end) HS5,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 5 and day(D.RQ) = 31 then D.RZY end) LZY5,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 6 and day(D.RQ) = 30 then D.DYM end) DYM6,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 6 and day(D.RQ) = 30 then D.RY end) RY6,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 6 and day(D.RQ) = 30 then D.RYOU end) RYOU6,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 6 and day(D.RQ) = 30 then D.HS end) HS6,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 6 and day(D.RQ) = 30 then D.RZY end) LZY6,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 7 and day(D.RQ) = 31 then D.DYM end) DYM7,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 7 and day(D.RQ) = 31 then D.RY end) RY7,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 7 and day(D.RQ) = 31 then D.RYOU end) RYOU7,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 7 and day(D.RQ) = 31 then D.HS end) HS7,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 7 and day(D.RQ) = 31 then D.RZY end) LZY7,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 8 and day(D.RQ) = 31 then D.DYM end) DYM8,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 8 and day(D.RQ) = 31 then D.RY end) RY8,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 8 and day(D.RQ) = 31 then D.RYOU end) RYOU8,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 8 and day(D.RQ) = 31 then D.HS end) HS8,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 8 and day(D.RQ) = 31 then D.RZY end) LZY8,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 9 and day(D.RQ) = 30 then D.DYM end) DYM9,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 9 and day(D.RQ) = 30 then D.RY end) RY9,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 9 and day(D.RQ) = 30 then D.RYOU end) RYOU9,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 9 and day(D.RQ) = 30 then D.HS end) HS9,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 9 and day(D.RQ) = 30 then D.RZY end) LZY9,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 10 and day(D.RQ) = 31 then D.DYM end) DYM10,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 10 and day(D.RQ) = 31 then D.RY end) RY10,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 10 and day(D.RQ) = 31 then D.RYOU end) RYOU10,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 10 and day(D.RQ) = 31 then D.HS end) HS10,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 10 and day(D.RQ) = 31 then D.RZY end) LZY10,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 11 and day(D.RQ) = 30 then D.DYM end) DYM11,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 11 and day(D.RQ) = 30 then D.RY end) RY11,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 11 and day(D.RQ) = 30 then D.RYOU end) RYOU11,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 11 and day(D.RQ) = 30 then D.HS end) HS11,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 11 and day(D.RQ) = 30 then D.RZY end) LZY11,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 12 and day(D.RQ) = 31 then D.DYM end) DYM12,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 12 and day(D.RQ) = 31 then D.RY end) RY12,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 12 and day(D.RQ) = 31 then D.RYOU end) RYOU12,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 12 and day(D.RQ) = 31 then D.HS end) HS12,
MAX(case when year(D.RQ) = 2022 and month(D.RQ) = 12 and day(D.RQ) = 31 then D.RZY end) LZY12
from DXTJ A
left join DEPT_JBXXB C WITH (FORCESEEK) on A.SGDW = C.DEPEID
left join CSJING_SCSJB D on A.JH = D.JH
left join GLY_JBXXB B on A.GLY = B.ID
where JB = '油井'
GROUP BY A.ID, A.JH, JB, C.DEPTNAME, YT, SFXT, ZDXYY, ZYNR, DXFL, TSQKMS, XXXT, SFTSZL, B.GLYMC, SJRQ, WJRQ, TCRQ, A.BZ
OPTION (ORDER GROUP)