mysql - 如何在两个字段上使用 COUNT 和 GROUP BY

Question

我正在尝试编写一个查询，其中将显示每个日期的所有建筑物，并为不存在的建筑物列出零。下面是我的示例的示例表和数据。

CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(6) unsigned NOT NULL,
  `buildings` char(3) NOT NULL,
  `date` varchar(50) NOT NULL,
  `sold` char(1) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `table1` (`id`, `buildings`, `date`, `sold`) VALUES
  ('1', '1', '2019-04-22 07:40:08','X'),
  ('2', '1', '2019-04-22 07:41:15',''),
  ('3', '1', '2019-04-22 07:42:10','X'),
  ('4', '3', '2019-04-22 07:43:50','X'),
  ('5', '1', '2019-04-22 07:44:27',''),
  ('6', '2', '2019-05-21 06:43:17','X'),
  ('7', '2', '2019-05-21 07:36:17',''),
  ('8', '1', '2019-05-21 06:32:22','X'),
  ('9', '3', '2019-05-21 07:43:50',''),
  ('10', '2', '2019-05-21 07:44:27','X');

下面列出了我目前使用的 SQL 查询。用于构建第一列的 CONCAT 和 MID 正在获取年份和年份中的星期以每周创建一个 4 位代码。

SELECT CONCAT(MID(date,3,2),
           LPAD(WEEK(CONCAT(MID(date,1,4),'-',
           MID(date,6,2),'-',
           MID(date,10,2))),2,0)) AS YearWeek, 
       buildings, 
       COUNT(sold) AS count 
FROM table1 
WHERE sold = 'X' 
GROUP BY CONCAT(MID(date,3,2),
           LPAD(WEEK(CONCAT(MID(date,1,4),'-',
           MID(date,6,2),'-',
           MID(date,10,2))),2,0)),
         buildings

我的问题是我得到的数据如下

YearWeek    buildings   count
1913        1           2
1913        3           1
1917        1           1
1917        2           2

事实上，我希望数据看起来像下面这样，以便我可以绘制它。

YearWeek    buildings   count
1913        1           2
1913        2           0
1913        3           1
1917        1           1
1917        2           2
1917        3           0

为了实现这一点，我尝试对子查询进行左连接(从表 1 中选择不同的建筑物作为建筑物)但这不起作用，我认为这是因为我按 YearWeek 分组然后 build 而不是相反。'

非常感谢对此查询的任何帮助!

Answer 1

您可以通过对不同的 YearWeek 和 buildings 值进行 CROSS JOIN 来获得所需的结果，以获得所有可能的组合每个，然后对表使用 LEFT JOIN 以获取当周售出的每种类型的 buildings 的数量。例如:

SELECT yw.YearWeek,
       b.buildings,
       COALESCE(SUM(t.sold = 'X'), 0) AS count
FROM (SELECT DISTINCT buildings
      FROM table1) b
CROSS JOIN (SELECT DISTINCT RIGHT(YEARWEEK(date),4) AS YearWeek
            FROM table1) yw
LEFT JOIN table1 t ON t.buildings = b.buildings AND RIGHT(YEARWEEK(t.date),4) = yw.YearWeek
GROUP BY yw.YearWeek, b.buildings

输出:

YearWeek    buildings   count
1916        1           2
1916        2           0
1916        3           1
1920        1           1
1920        2           2
1920        3           0

Demo on dbfiddle

注意我使用了 MySQL 的内置 YEARWEEK函数来简化查询，如果不合适，您可以简单地替换您的公式。