我有以下查询,并且由于很多SUM
函数调用,我的查询运行太慢。我的数据库中有很多记录,我想为每条记录获取今年和去年(过去 30 天、过去 90 天和过去 365 天)的报告:
SELECT
b.id as [ID]
,d.[Title] as [Title]
,e.Class as [Class]
,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Current - Last 30 Days Col1]
,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Current - Last 30 Days Col2]
,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Current - Last 90 Days Col1]
,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Current - Last 90 Days Col2]
,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Current - Last 365 Days Col1]
,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Current - Last 365 Days Col2]
,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-13,GETDATE()) and a.DateCol <= DATEADD(MONTH,-12,GETDATE()) THEN a.col1 ELSE 0 END) as [Last year - Last 30 Days Col1]
,Sum(CASE WHEN a.DateCol >= DATEADD(MONTH,-13,GETDATE()) and a.DateCol <= DATEADD(MONTH,-12,GETDATE()) THEN a.col2 ELSE 0 END) as [Last year - Last 30 Days Col2]
,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-5,GETDATE()) and a.DateCol <= DATEADD(QUARTER,-4,GETDATE()) THEN a.col1 ELSE 0 END) as [Last year - Last 90 Days Col1]
,Sum(CASE WHEN a.DateCol >= DATEADD(QUARTER,-5,GETDATE()) and a.DateCol <= DATEADD(QUARTER,-4,GETDATE()) THEN a.col2 ELSE 0 END) as [Last year - Last 90 Days Col2]
,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-2,GETDATE()) and a.DateCol <= DATEADD(YEAR,-1,GETDATE()) THEN a.col1 ELSE 0 END) as [Last year - Last 365 Days Col1]
,Sum(CASE WHEN a.DateCol >= DATEADD(YEAR,-2,GETDATE()) and a.DateCol <= DATEADD(YEAR,-1,GETDATE()) THEN a.col2 ELSE 0 END) as [Last year - Last 365 Days Col2]
FROM
tb1 a
INNER JOIN
tb2 b on a.id=b.fid and a.col3 = b.col4
INNER JOIN
tb3 c on b.fid = c.col5
INNER JOIN
tb4 d on c.id = d.col6
INNER JOIN
tb5 e on c.col7 = e.id
GROUP BY
b.id, d.Title, e.Class
有谁知道如何改进我的查询以便运行得更快?
EDIT:我被鼓励搬家DATEADD
函数调用where
语句并首先加载前两年,然后在列中过滤它们,但我不确定建议的答案是否被执行和工作,可以在这里找到:https://stackoverflow.com/a/59944426/12536284 https://stackoverflow.com/a/59944426/12536284
如果您同意上述解决方案,请告诉我如何将其应用到当前的查询中?
仅供参考,我在 C#、实体框架(DB-First)中使用这个 SP,如下所示:
var result = MyDBEntities.CalculatorSP();