在 where 子句中使用自联接和 group bys 优化查询

debugcn 发表于 Dev

PBJ

这是我的旧查询：

Select AccountID,AccountDesc,
    CASE 
    WHEN NOT EXISTS (select AccountID from #B where #B.AccountID = P1.AccountID) THEN cast(cast(GETDATE() as date) as varchar)     
    ELSE cast(cast(CreateDate as date) as varchar)
    END 
    FROM #A P1
    WHERE P1.EndDate = (Select max(EndDate) from #A P2 where P1.AccountID = P2.AccountID and P2.Categ1 = 'F'
    group by P2.AccountID) 
    and P1.StartDate = (Select max(StartDate) from #A P2 where P1.AccountID = P2.AccountID and P2.Categ1 = 'F'
    group by P2.AccountID) 
    and P1.CreateDate = (Select max(CreateDate ) from #A P2 where P1.AccountID = P2.AccountID and P2.Categ1 = 'F'
    group by P2.AccountID) 
    and P1.NewStartDate= (Select max(NewStartDate) from #A P2 where P1.AccountID = P2.AccountID and P2.Categ1 = 'F'
    group by P2.AccountID)

我想在 where 子句中摆脱重复的自连接和 group bys，所以我摆脱了“Categ1 = F”部分，并将它放在工作表中 #A 的 where 子句中，而不是在我插入它时. 而且，我认为自联接只是为了获取 MAX 值，所以自从我制作了工作表，我认为我不再需要这种自联接。所以，我最终得到了这个新查询：

Select AccountID,AccountDesc,
    CASE 
    WHEN NOT EXISTS (select AccountID from #B where #B.AccountID = P1.AccountID) THEN cast(cast(GETDATE() as date) as varchar)     
    ELSE cast(cast(CreateDate as date) as varchar)
    END 
    FROM #A P1
    WHERE P1.EndDate = (Select max(EndDate) from #A)
    and P1.StartDate = (Select max(StartDate) from #A) 
    and P1.CreateDate = (Select max(CreateDate) from #A) 
    and P1.NewStartDate= (Select max(NewStartDate) from #A)

但这不是返回相同数量的记录，我相信是因为我还删除了 where 条件中的“Group By”子句？

有人可以帮助我更快地进行这个原始查询，但仍然返回相同的结果吗？

基思

您可以创建一个子查询并将其加入，以便计算发生一次。

;with cte as
(
select Account,max(EndDate) ED ,max(StartDate) SD,max(CreateDate ) CD ,max(NewStartDate) NSD 
from #a
where Categ1 = 'F'
group by Account
)
Select AccountID,AccountDesc,
    CASE 
    WHEN NOT EXISTS (select AccountID from #B where #B.AccountID = P1.AccountID) THEN cast(cast(GETDATE() as date) as varchar)     
    ELSE cast(cast(CreateDate as date) as varchar)
    END 
 FROM #A p1
     join cte on p1.Account=cte.Account
           and p1.StartDate=cte.SD
           and p1.EndDate = cte.ED
           and p1.CreateDate = cte.CD 
           and p1.NewStartDate=cte.NSD

这是另一个增强功能：

CASE 
    WHEN NOT EXISTS (select AccountID from #B where #B.AccountID = P1.AccountID) THEN cast(cast(GETDATE() as date) as varchar)     
    ELSE cast(cast(CreateDate as date) as varchar)

可以通过左加入#B来改变

;with cte as
(
select Account,max(EndDate) ED ,max(StartDate) SD,max(CreateDate ) CD ,max(NewStartDate) NSD 
from #a
where Categ1 = 'F'
group by Account
)
Select AccountID,AccountDesc,
    case when p2.Account is not null 
         then cast(CreateDate as varchar(10)) 
         else cast(cast(getdate() as date) as varchar(10)) end
 FROM #A p1
     join cte on p1.Account=cte.Account
           and p1.StartDate=cte.SD
           and p1.EndDate = cte.ED
           and p1.CreateDate = cte.CD 
           and p1.NewStartDate=cte.NSD
     left join #B p2 on p1.Account=p2.Account

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。