将不存在或不存在的数据规范化为单个sql查询

pprl5pva 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(374)

我的table上有这张唱片。一些身份证没有参加课程，因此将没有记录存在。只有3个类可用，即c、tp和tt。

ID        Class    Month     Year    Finished    Total 
ASAN014    C        04       2020      12         19
ASAN014    TP       04       2020      4          6
ASAN014    TT       04       2020      2          7
ASAN014    C        05       2020      10         18
ASAN014    TP       05       2020      1          2
ASAN014    TT       05       2020      2          6
ASAN015    C        04       2020      21         24
ASAN015    TP       04       2020      0          1
ASAN015    TT       04       2020      8          11
ASAN015    C        05       2020      14         19
ASAN015    TT       05       2020      4          5

如前所述。如果asan015没有参加tt的类，那么finishedtt和totaltt的数据缺省值应该为零。我需要按id、月份和年份对它进行分组，其中它将如下表所示：

ID        Month    Year   FinishedC TotalC   FinishedTP  TotalTP  FinishedTT  TotalTT
ASAN014     04     2020      12      19          4        6         2          7
ASAN014     05     2020      10      18          1        2         2          6
ASAN015     04     2020      21      24          0        1         8          11
ASAN015     05     2020      14      19          4        5         0          0

我的问题是我们能在一个查询中完成吗？

sql mysql normalization

来源：https://stackoverflow.com/questions/61955257/normalize-data-with-no-matter-exist-or-no-exist-into-single-sql-query

1条答案

按热度按时间

xjreopfe1#

可以使用条件聚合。

SELECT
    ID,
    Month,
    Year,
    SUM(CASE WHEN Class = 'C' THEN Finished ELSE 0 END) AS FinishedC,
    SUM(CASE WHEN Class = 'C' THEN Total ELSE 0 END) AS TotalC,
    SUM(CASE WHEN Class = 'TT' THEN Finished ELSE 0 END) AS FinishedTT,
    SUM(CASE WHEN Class = 'TT' THEN Total ELSE 0 END) AS TotalTT,
    SUM(CASE WHEN Class = 'TP' THEN Finished ELSE 0 END) AS FinishedTP,
    SUM(CASE WHEN Class = 'TP' THEN Total ELSE 0 END) AS TotalTP
FROM 
 TblFinishByClass
GROUP BY
    ID,
    Month,
    Year;

结果：

+----+---------+-------+------+-----------+--------+------------+---------+------------+---------+
|    |   ID    | Month | Year | FinishedC | TotalC | FinishedTT | TotalTT | FinishedTP | TotalTP |
+----+---------+-------+------+-----------+--------+------------+---------+------------+---------+
|  1 | ASAN014 |     4 | 2020 |        12 |     19 |          2 |       7 |          4 |       6 |
|  2 | ASAN014 |     5 | 2020 |        10 |     18 |          2 |       6 |          1 |       2 |
|  3 | ASAN015 |     4 | 2020 |        21 |     24 |          8 |      11 |          0 |       1 |
|  4 | ASAN015 |     5 | 2020 |        14 |     19 |          4 |       5 |          0 |       0 |
+----+---------+-------+------+-----------+--------+------------+---------+------------+---------+

赞(0）回复(0）举报 2021-08-13

我来回答

将不存在或不存在的数据规范化为单个sql查询

1条答案

相关问题

热门标签

最新问答