时间戳

首页 > 解决方案 > 缩短重复的 SQL 查询

sql - 缩短重复的 SQL 查询

问题描述

有没有更好的方法来编写以下代码，可能是某种循环等。在此之前，我在查询中创建了一个临时表。然后我尝试创建不同的列，这些列按时间顺序为我提供了在一行中发生在客户帐户上的操作。

SELECT 
    a.*,
    Action_1 = (SELECT Rating_Changed_To FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '1' )),
    Action_1_Time = (SELECT Change_Date FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '1' )),
    Action_2 = (SELECT Rating_Changed_To FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '2' )),
    Action_2_Time = (SELECT Change_Date FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '2' )),
    Action_3 = (SELECT Rating_Changed_To FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '3' )),
    Action_3_Time = (SELECT Change_Date FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '3' )),
    Action_4 = (SELECT Rating_Changed_To FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '4' )),
    Action_4_Time = (SELECT Change_Date FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '4' )),
    Action_5 = (SELECT Rating_Changed_To FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '5' )),
    Action_5_Time = (SELECT Change_Date FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '5' )),
    Action_6 = (SELECT Rating_Changed_To FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '6' )),
    Action_6_Time = (SELECT Change_Date FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '6' )),
    Action_7 = (SELECT Rating_Changed_To FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '7' )),
    Action_7_Time = (SELECT Change_Date FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '7' )),
    Action_8 = (SELECT Rating_Changed_To FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '8' )),
    Action_8_Time = (SELECT Change_Date FROM #TempICHist WHERE (IC_ID_18 = a.ICID_18 AND Rank = '8' ))
FROM
    #TempICs a
ORDER BY
    a.Programme,a.IC_Created_Date

我希望问题很清楚，但如果有混淆，我可以回答。

标签： sqlsql-server

解决方案

一种方法是使用apply条件聚合：

select a.*, Act_1.*
from #tempics a outer apply
     ( SELECT MAX(CASE WHEN RANK = 1 THEN Rating_Changed_To END) AS Action_1,
              MAX(CASE WHEN RANK = 2 THEN Rating_Changed_To END) AS Action_2,
              . . . 
              MAX(CASE WHEN RANK = 1 THEN Change_Date END) AS Action_1_Time,
              MAX(CASE WHEN RANK = 2 THEN Change_Date END) AS Action_2_Time,
              . . . 
       FROM #TempICHist 
       WHERE IC_ID_18 = a.ICID_18 AND Rank IN (1,2,3,4,5,6,7,8)
     ) Act_1;

推荐阅读