Как использовать COUNT с GROUP BY для двух полей
Я пытаюсь написать запрос, в котором для каждой даты будут отображаться все здания, а для несуществующих указаны нули. Ниже приведен пример таблицы и данных для моего примера.
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, а затем строю, а не наоборот».
Любая помощь по этому запросу будет высоко оценена!
Ответы 1
Вы можете получить желаемые результаты, взяв CROSS JOIN различных значений YearWeek и buildings, чтобы получить все возможные комбинации каждого, а затем используя 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
Примечание. Я использовал встроенную в MySQL функцию YEARWEEK для упрощения запроса, вы можете просто заменить свою формулу, если она не подходит.
Спасибо! Это сделало это! Я не знал о перекрестном соединении, мне придется больше разбираться в этом. К сожалению, мне нужна моя сумасшедшая формула, так как дата, с которой я работаю, не такая чистая, я изменил ее, чтобы попытаться сделать пример немного чище, но, возможно, я просто добавил путаницы.
@ Элмер, не беспокойся. CROSS JOIN хорош для составления таблицы со всеми комбинациями двух разных переменных. Должно быть достаточно легко заменить вашу формулу обратно; это просто необходимо в двух местах.
Вам нужно соединить подзапрос, получающий разные здания, с подзапросом, который получает разные недели года, а затем соединить таблицу с этим.