使用case语句在SQL查询中创建新列

Question

我已经进行了一些表连接操作，以下是我的数据外观。

Cust_No Account_No  Product_H_f Product_H_L
123     A9023         Core      Training
123     A9023         Core      Training
834     A9023         Core      Complimentary
634     A9024         Core      Complimentary
264     A9025         DTB       Training
263     A9025         DTB       Complimentary
677     A9025         DTB       Training

我想在这个结果中添加一列，名为“接受培训的客户编号”，应该长这样。（我的原始查询非常复杂，包括6个表的左连接）

Cust_No Account_No  Product_H_f Product_H_L Cust_has_Training
123    A9023        Core        Training        Yes
123    A9023        Core        Training        Yes
834    A9023        Core        Complimentary   Yes 
834    A9023        Core        Training        Yes
634    A9024        Core        Complimentary   No
264    A9025        DTB         Training        Yes
263    A9025        DTB         Complimentary   No
677    A9025        DTB         Training        Yes
677    A9025        DTB         Basic           Yes

我正在使用以下代码来实现这个功能，看起来它正在工作。但是由于我有4800万条记录，运行时间太长了。有没有办法可以优化这个查询？

SELECT Cust_No, 
       Account_No,  
       Product_H_f, 
       Product_H_L,
(CASE WHEN Cust_No IN (SELECT distinct(Cust_No) from table where 
Product_H_L='Training') then 'Yes' else 'No' end) as 'Cust_has_Training'
FROM TABLE 

- Mili

---

使用EXISTS可能会得到更好的性能，但通过连接到一个视图或执行选择查询的子查询，性能很可能同样出色。 - UnhandledExcepSean

我尝试使用EXISTS，但不知何故它给出了错误的结果。它确实运行得更快。 - Mili

Answer 1

如果您有一个复杂的查询，最有效（也是最简单）的添加新计算的方法可能基于 窗口聚合：

SELECT Cust_No, 
       Account_No,  
       Product_H_f, 
       Product_H_L,
       -- returns YES when any row for a customer has 'Training'
       MAX(CASE WHEN Product_H_L='Training' THEN 'Yes' else 'No' end)
       OVER (PARTITION BY Cust_No) as 'Cust_has_Training'
FROM TABLE 

- dnoeth

---

在这种情况下，max()函数是做什么的？谢谢。 - moth

@moth：根据字母顺序，如果同一分区中的任何行返回YES，则此Group Max将为所有行分配YES。如果您切换到MIN，则如果任何行返回NO，则为NO。 - dnoeth

Answer 2

对于这个目的，EXISTS 和子查询连接都可以表现得非常好。如果没有提供更多信息，你可能不会得到比通用的“这通常更快”的更好的答案。

SELECT Cust_No, 
       Account_No,  
       Product_H_f, 
       Product_H_L,
(CASE WHEN Cust_has_Training.[Cust_No] is not null then 'Yes' else 'No' end) as 'Cust_has_Training'
FROM TABLE 
LEFT JOIN (
   SELECT distinct(Cust_No) AS [Cust_No]
   from table where 
   Product_H_L='Training'
) Cust_has_Training ON Cust_has_Training.[Cust_No]=Table.[Cust_No]

- UnhandledExcepSean

---

@Mili……我会期望窗口函数或者 exists 执行起来更快。你能详细阐述一下你为什么接受了这个答案吗？ - Gordon Linoff

Answer 3

如何使用EXISTS()？

SELECT Cust_No, 
       Account_No,  
       Product_H_f, 
       Product_H_L,
(CASE WHEN EXISTS(SELECT 1 FROM TABLE WHERE Cust_No = T1.Cust_No AND
Product_H_L='Training') then 'Yes' else 'No' end) as 'Cust_has_Training'
FROM TABLE T1

- Ilyes