mysql - 返回零与组和在MySQL中计数

我正在计算不同客户的数量并按另一列对其进行分组。如果在组中找不到客户，我希望结果返回零。使用下面的代码，我的结果不会为零。有人能帮忙吗？

SELECT download_date,resolution,COUNT(DISTINCT customer_id) FROM test.sample
GROUP BY download_date,resolution;

这给了我以下信息：

----------------------------------------
| 2011-06-09| HD       | 1             |
----------------------------------------
| 2011-06-09| SD       | 1             |
----------------------------------------
| 2012-06-10| SD       | 1             |
----------------------------------------

我希望得到以下信息：

----------------------------------------
| 2011-06-09| HD       | 1             |
----------------------------------------
| 2011-06-09| SD       | 1             |
----------------------------------------
| 2012-06-10| HD       | 1             |
----------------------------------------
| 2012-06-10| SD       | 0             |
----------------------------------------

下面是表格结构：

CREATE TABLE test.sample (
`customer_id` VARCHAR(15) NULL DEFAULT NULL,
`download_date` DATE NULL DEFAULT NULL,
`resolution` VARCHAR(2) NULL DEFAULT NULL,
`total_units` INT(11) NULL DEFAULT NULL);

INSERT INTO test.sample(`customer_id`,`download_date`,`resolution`,`total_units`)
VALUES('1','2012-06-10','HD',40),('1','2012-06-10','HD',20),
('2','2011-06-09','SD',10),('2','2011-06-09','HD',20);

最佳答案

select dr.download_date, r.resolution,COUNT(DISTINCT customer_id)
from
(select DISTINCT download_date from sample) dr
cross join
(select distinct resolution from sample) r
left join sample s on s.download_date = dr.download_date
and r.resolution = s.resolution
group by dr.download_date, r.resolution;

FIDDLE

关于mysql - 返回零与组和在MySQL中计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17750509/