时间戳

首页 > 解决方案 > Sql - 分组表

mysql - Sql - 分组表

问题描述

这是我的查询；

    SELECT Name, MAX(A) AS A, MAX(B) AS B
    FROM (
    SELECT l.Name, SUM(A.Count) AS A, 0 AS B
    FROM List l
    LEFT JOIN A ON A.Name = l.Name
    GROUP BY l.Name
    UNION ALL
    SELECT l.Name, 0 AS A, SUM(B.Count)AS B
    FROM List l
    LEFT JOIN B ON B.Name = l.Name
    GROUP BY l.Name) sub
    GROUP BY Name
    ORDER BY Name

我的表格和查询结果在这里：https ://www.db-fiddle.com/f/rdKLkyaeEsi8bPcNPkUnTE/11

我需要在 C.createDate(ex;2017-2018-2019) 上将 Table-A 与 Table-C 分组

每年 -> 添加列

表 A 中的 C_ID -> 表 C 中的 ID

2017 -> 编号：3,5

2018 -> 编号：2,6,8

2019 -> 编号：1,4,7

示例：对于产品 1（仅表 A 值）

2017 -> 13 ; 2018 -> 23+11 = 34；2019 -> 5 ; B -> 8+10=18（2018 年为 8，2017 年为 10，但年份对表 B 不重要）

例如，我的目标结果；

    Name     |  2017  |  2018  |  2019  |  B  
    ---------+--------+--------+--------+------
    Product1 |   13   |   34   |   5    |  18
    Product2 |   21   |   0    |   3    |  0
    Product3 |   13   |   0    |   7    |  15
    Product4 |   18   |   27   |   0    |  29
    Product5 |   0    |   8    |   25   |  32
    Product6 |   0    |   0    |   0    |  0
    Product7 |   22   |   14   |   0    |  0
    Product8 |   0    |   26   |   4    |  23
    Product9 |   0    |   11   |   0    |  0

标签： mysqlsql

解决方案

我推荐与 Thorsten Kettner 回答相同的方式。但我认为你必须使用CASE和总和A.COUNT。您可以使用YEAR()从日期中提取年份，然后SUM使用case相应年份的语句。

SELECT List.NAME,IFNULL(MYAC.2017,0) AS '2017', IFNULL(MYAC.2018,0) AS '2018', IFNULL(MYAC.2019,0) AS '2019',IFNULL(BSUM.TOTAL,0) as B
FROM
(
  SELECT
    A.NAME,
    SUM(case when YEAR(C.CREATEDATE) = 2017 then A.Count else 0 end) as '2017',
    SUM(case when YEAR(C.CREATEDATE) = 2018 then A.Count else 0 end) AS '2018',
    SUM(case when YEAR(C.CREATEDATE) = 2019 then A.Count else 0 end) AS '2019'
  FROM A
  LEFT JOIN C ON C.ID = A.C_ID
  GROUP BY A.NAME
) MYAC
LEFT JOIN
(
  SELECT NAME,SUM(B.COUNT) AS TOTAL
  FROM B
  GROUP BY NAME
) BSUM ON BSUM.NAME = MYAC.NAME
RIGHT JOIN
List on List.Name = MYAC.NAME
ORDER BY List.NAME;

在此处检查输出小提琴

更新您可以使用LEFT JOIN下面的答案也RIGHT JOIN经常影响可读性。

SELECT List.Name,IFNULL(MYABC.2017,0) AS '2017', IFNULL(MYABC.2018,0) AS '2018', IFNULL(MYABC.2019,0) AS '2019',IFNULL(MYABC.B,0) as B from List
LEFT JOIN (
SELECT MYAC.Name,MYAC.2017 AS '2017', MYAC.2018 AS '2018', MYAC.2019 AS '2019',BSUM.TOTAL as B
FROM
(
  SELECT
    A.NAME,
    SUM(case when YEAR(C.CREATEDATE) = 2017 then A.Count else 0 end) as '2017',
    SUM(case when YEAR(C.CREATEDATE) = 2018 then A.Count else 0 end) AS '2018',
    SUM(case when YEAR(C.CREATEDATE) = 2019 then A.Count else 0 end) AS '2019'
  FROM A
  LEFT JOIN C ON C.ID = A.C_ID
  GROUP BY A.NAME
) MYAC
LEFT JOIN
(
  SELECT NAME,SUM(B.COUNT) AS TOTAL
  FROM B
  GROUP BY NAME
) BSUM ON BSUM.NAME = MYAC.NAME ) MYABC
on List.name = MYABC.name
ORDER BY List.NAME;

在此处使用左连接检查输出小提琴

推荐阅读