case语句创建的列不按 [英] Columns created by case statement do not group by

问题描述

这是T-SQL查询：

SELECT 
    A.DateStamp,
    CASE WHEN A.T = 10 THEN A.counts END AS HT,  
    CASE WHEN A.T = 98 THEN A.counts END AS BP,
    CASE WHEN A.T = 94 THEN A.counts END AS MP,
    CASE WHEN A.T = 12 THEN A.counts END AS SP
FROM  
    A                        
WHERE 
    (A.date_time BETWEEN GETDATE() - 60 AND GETDATE() - 30)   -- say 
--GROUP BY A.DateStamp,A.T,A.counts
ORDER BY
    CONVERT(DATE, A.DateStamp) ASC

效果很好。以前我使用的是表A的多个副本，并且所有副本都已加入。此处的结果是正确的，但是分成多个行，例如：

It works well. Previously I was using multiple copies of table A and all joined. over here results are correct but split in multiple rows like:

date       | BP | MP | SP | HT |
-----------+----+----+----+----+
22/10/2017   12   34   56   78
Looks Like   --   --   --   --
22/10/2017   12   --   --   --
22/10/2017   --   34   --   --
22/10/2017   --   --   56   --
22/10/2017   --   --   --   78

推荐答案

为此，您需要使用条件聚合，而 GROUP BY 只是 DateStamp 字段：

You need to use conditional aggregation for this and GROUP BY just the DateStamp field:

SELECT A.DateStamp,
       SUM(CASE WHEN A.T=10 THEN A.counts ELSE 0 END) AS HT,  
       SUM(CASE WHEN A.T=98 THEN A.counts ELSE 0 END) AS BP,
       SUM(CASE WHEN A.T=94 THEN A.counts ELSE 0 END) AS MP,
       SUM(CASE WHEN A.T=12 THEN A.counts ELSE 0 END) AS SP
FROM  A                        
WHERE (A.date_time BETWEEN getdate()-60 AND getdate()-30) 
GROUP BY A.DateStamp
ORDER BY convert(date,A.DateStamp) ASC

这篇关于case语句创建的列不按的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！