sql - 是否可以在一个查询中按不同条件计数？

Question

我有一个 Shipments 表，它基本上包含带有日期的 Shipments 数据

id 是整数dateshipped 是 date

id    dateshipped
1     1-JAN-16
2     1-JAN-16
3     3-FEB-16
4     9-FEB-16  

我想编写一个查询，根据月份计算所有发货。我应该得到的是:

Jan  Feb  March....
2     2     0

我知道我可以通过查询每一列来做到这一点，只获取这个特定月份的相关行并计算它们。

如下:

Select     (Select count(*)
           from Shipments
           Where EXTRACT(YEAR FROM dateshipped)::int=2016 and EXTRACT(MONTH FROM dateshipped)::int=1 )as JAN,
           (Select count(*)
           from Shipments
           Where EXTRACT(YEAR FROM dateshipped)::int=2016 and EXTRACT(MONTH FROM dateshipped)::int=2 )as FEB

这可行，但是它有太多相同的代码...

我想知道是否可以使用单个 FROM 语句来做到这一点，并且每一列都有自己的相关行以进行计数。

类似于:

Select COL1,COL2,COL3...
from Shipments
Where EXTRACT(YEAR FROM dateshipped)::int=2016;

并有类似的东西:

COL1 = count only JAN records
COL2 = count only FEB records
....

也许月或任何其他解决方案与 Parations 有什么关系？

Answer 1

您需要一个数据透视查询来完成此操作:

SELECT SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 1  THEN 1 ELSE 0 END) AS Jan,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 2  THEN 1 ELSE 0 END) AS Feb,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 3  THEN 1 ELSE 0 END) AS Mar,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 4  THEN 1 ELSE 0 END) AS Apr,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 5  THEN 1 ELSE 0 END) AS May,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 6  THEN 1 ELSE 0 END) AS Jun,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 7  THEN 1 ELSE 0 END) AS Jul,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 8  THEN 1 ELSE 0 END) AS Aug,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 9  THEN 1 ELSE 0 END) AS Sep,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 10 THEN 1 ELSE 0 END) AS Oct,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 11 THEN 1 ELSE 0 END) AS Nov,
       SUM(CASE WHEN EXTRACT(MONTH FROM dateshipped)::int = 12 THEN 1 ELSE 0 END) AS Dec
FROM Shipments
WHERE EXTRACT(YEAR FROM dateshipped)::int=2016