使用多个 CASE 语句查询 - 优化 [英] Query with many CASE statements - optimization

问题描述

我有一个非常脏的查询，肯定可以优化，因为其中有很多 CASE 语句！

I have one very dirty query that per sure can be optimized because there are so many CASE statements in it!

SELECT 
    (CASE pa.KplusTable_Id WHEN 1 THEN sp.sp_id 
          WHEN 2 THEN fw.fw_id
          WHEN 3 THEN s.sw_Id
          WHEN 4 THEN id.ia_id END) as Deal_Id,
max(CASE pa.KplusTable_Id WHEN 1 THEN sp.Trans_Id 
                          WHEN 2 THEN fw.Trans_Id
                          WHEN 3 THEN s.Trans_Id
                          WHEN 4 THEN id.Trans_Id END) as TransId_CurrentMax
INTO #MaxRazlicitOdNull
FROM #PotencijalniAktuelni pa LEFT JOIN kplus_sp sp (nolock) on sp.sp_id=pa.Deal_Id AND pa.KplusTable_Id=1
    LEFT JOIN kplus_fw fw (nolock) on fw.fw_id=pa.Deal_Id AND pa.KplusTable_Id=2        
    LEFT JOIN dev_sw s (nolock) on s.sw_Id=pa.Deal_Id AND pa.KplusTable_Id=3
    LEFT JOIN kplus_ia id (nolock) on id.ia_id=pa.Deal_Id AND pa.KplusTable_Id=4
WHERE isnull(CASE pa.KplusTable_Id WHEN 1 THEN sp.BROJ_TIKETA 
                                   WHEN 2 THEN fw.BROJ_TIKETA
                                   WHEN 3 THEN s.tiket
                                   WHEN 4 THEN id.BROJ_TIKETA END, '')<>'' 
GROUP BY CASE pa.KplusTable_Id WHEN 1 THEN sp.sp_id 
                               WHEN 2 THEN fw.fw_id
                               WHEN 3 THEN s.sw_Id
                               WHEN 4 THEN id.ia_id END

因为我有几次相同的情况，你知道如何优化查询，让它更简单更好.欢迎所有建议！

Because I have same condition couple times, do you have idea how to optimize query, make it simpler and better. All suggestions are welcome!

提前 TnX！

内曼加

推荐答案

对我来说，这看起来像是一个拙劣的子类型尝试.这就是我认为你现在拥有的.

To me this looks like a botched attempt in sub-typing. This is what I think you have now.

基于模型，以下应该可以工作:

Based on the model, the following should work:

;
with
q_00 as (
    select
         pa.Deal_Id                                                             as Deal_Id
       , coalesce(sp.BROJ_TIKETA, fw.BROJ_TIKETA, sw.tiket, ia.BROJ_TIKETA, '') as Ticket_No
       , coalesce(sp.Trans_Id, fw.Trans_Id, sw.Trans_Id, ia.Trans_Id)           as Trans_Id
    from #PotencijalniAktuelni as pa
    left join kplus_sp         as sp on sp.sp_Id = pa.Deal_Id and pa.KplusTable_Id = 1
    left join kplus_fw         as fw on fw.fw_Id = pa.Deal_Id and pa.KplusTable_Id = 2        
    left join dev_sw           as sw on sw.sw_Id = pa.Deal_Id and pa.KplusTable_Id = 3
    left join kplus_ia         as ia on ia.ia_Id = pa.Deal_Id and pa.KplusTable_Id = 4
)
select
      Deal_Id
    , max(Trans_Id) as TransId_CurrentMax
into #MaxRazlicitOdNull
from  q_00
where Ticket_No <> ''
group by Deal_Id ;

SQL Server 2005 +

SQL Server 2005 +

这篇关于使用多个 CASE 语句查询 - 优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！