C#实习生
2022-03-17 11:18
采纳率: 60%
浏览 33

百万级别sql server 数据库 sql语句优化

这条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)

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

1条回答 默认 最新

相关推荐 更多相似问题