sql-server - SQL Group By - 从单列生成多个聚合列

Question

我想按公司和日期进行分组，并为 2 个单独的值(Flag=Y 和 Flag=N)生成计数列。输入表如下所示:

Company  Date   Flag
------- ------- -----
001      201201  Y
001      201201  N
001      201202  N
001      201202  N
001      201202  Y

输出应如下所示:

Company Date   Count_Y Count_N
------- ------ ------- -------
001     201201  1       1
001     201202  1       2

如何编写 SQL 查询？任何形式的帮助表示赞赏!谢谢!

Answer 1

您可以使用相关子查询来完成此操作，如下所示:

SELECT 
  Company, 
  Date, 
  (SELECT COUNT(*) FROM MyTable AS T1 
     WHERE T1.Flag='Y' AND T1.Company=T2.Company AND T1.Date=T2.Date) AS Count_Y,
  (SELECT COUNT(*) FROM MyTable AS T1 
     WHERE T1.Flag='N' AND T1.Company=T2.Company AND T1.Date=T2.Date) AS Count_N
FROM MyTable AS T2
GROUP BY Company, Date

您还可以更简洁地完成此操作，但使用 SUM 技巧可能(可以说)可读性稍差:

SELECT 
  Company, 
  Date, 
  SUM(CASE WHEN Flag='Y' THEN 1 ELSE 0 END) AS Count_Y,
  SUM(CASE WHEN Flag='N' THEN 1 ELSE 0 END) AS Count_N,
FROM MyTable
GROUP BY Company, Date

在Oracle/PLSQL中，可以使用DECODE函数来替换CASE，以使其更加简洁:

SELECT 
  Company, 
  Date, 
  SUM(DECODE(Flag,'Y',1,0)) AS Count_Y,
  SUM(DECODE(Flag,'N',1,0)) AS Count_N,
FROM MyTable
GROUP BY Company, Date