SQL:如何选择共享 NAME 字段的多个 COUNT 值

Question

我有一个名为STUDENTS的APACHE DERBY数据库表，其中包含以下行：

_____________________________
| NAME  |COUNTER|   MONTH   |
-----------------------------
| bill  |   10  |   January |
-----------------------------
| bill  |   12  |   February|
-----------------------------
| bill  |   11  |   March   |
-----------------------------
| sam   |   14  |   January |
-----------------------------
| sam   |   9   |   February|
-----------------------------
| sam   |   22  |   March   |
-----------------------------
| hal   |   21  |   January |
-----------------------------
| hal   |   18  |   February|
-----------------------------
| hal   |   19  |   March   |
-----------------------------

我正在尝试找到语法以获得以下输出...

_________________________________
| NAME  |  JAN  |  FEB  |   MAR |
---------------------------------
| bill  |  10   |  12   |  11   |
---------------------------------
| sam   |  14   |   9   |  22   |
---------------------------------
| hal   |  21   |  18   |  19   |
---------------------------------

我尝试了以下方法：

select distinct(name), 
    (SELECT COUNTER FROM STUDENTS WHERE NAME = 'bill' AND month = 'january') as jan,
    (SELECT COUNTER FROM STUDENTS WHERE NAME = 'bill' AND month = 'february') as feb,
    (SELECT COUNTER FROM STUDENTS WHERE NAME = 'bill' AND month = 'march') as mar
    FROM STUDENT where name = 'bill'

。它在某种程度上可以作为一个学生的概念证明。但是，它很冗长，它仅适用于一个学生，并且它不能针对来自我将添加到数据库的未来月份。这意味着，我希望能够为每个 NAME 获取最后 3 个 COUNTER 值，无论数据库中有多少个月的数据退出。

我已经查看了多个与嵌套查询相关的 s.o. 问题，但没有一个有帮助。

建议？

任何帮助不胜感激！

Answer 1

您可以使用条件聚合

SELECT name,
       Max(CASE WHEN month = 'January'  THEN counter end) AS jan,
       Max(CASE WHEN month = 'February' THEN counter end) AS feb,
       Max(CASE WHEN month = 'March'    THEN counter end) AS mar
  FROM student
 GROUP BY name