sql - 摆脱昂贵的自连接

Question

我有一个这样的 SQL 语句

SELECT  
    pa.col1,
    SUM(ps.col2) col2,
    SUM(psl.col2) col2_previous_month
FROM  
    pa
LEFT JOIN 
    ps ON pa.Id = ps.Id AND ps.date = @currDate
LEFT JOIN 
    ps as psl ON psl.Id = ps.Id AND psl.date = dateadd(month, - 1, @currDate)
GROUP BY 
    pa.col1;

这个 SQL 经常被调用，因为表 ps有 100M 行左连接正在伤害。有没有办法使用 left Join 重写它？

问候
缺口

Answer 1

也许这会有所帮助

    Select pa.col1
          ,col2      =isnull(sum(case when ps.date=@currDate                   then ps.col2 else null end),0)
          ,col2_prior=isnull(sum(case when ps.date=dateadd(month,-1,@currDate) then ps.col2 else null end),0)
     From pa
     JOIN ps as ps ON pa.Id  = ps.Id 
      and ps.date in (@currDate,dateadd(month,-1,@currDate))
     Group By pa.col1