具有“默认值”的 SQL GROUP BY
我正在尝试使用 GROUP BY 子句创建 SELECT 语句，该语句应返回“默认值”。
想象一下下面的简单 MySQL 表：
CREATE TABLE `tracker` (
`id` INTEGER PRIMARY KEY auto_increment,
`date` DATETIME NOT NULL,
`customer_id` INTEGER NOT NULL
);
该表只包含一条记录：
INSERT INTO `tracker` (`date`, `customer_id`) VALUES('2010-05-03', 1);
之后我执行以下 SQL 查询：
SELECT DATE(`date`), COUNT(customer_id) FROM tracker
WHERE DATE(`date`) >= '2010-05-01' AND DATE(`date`) <= '2010-05-05'
GROUP BY DATE(`date`) ORDER BY DATE(`date`);
并得到预期的结果集：
+----+---------------------+-------------+
| id | date | customer_id |
+----+---------------------+-------------+
| 1 | 2010-05-10 00:00:00 | 1 |
+----+---------------------+-------------+
但是，我希望结果集看起来像这样：
+--------------+--------------------+
| DATE(`date`) | COUNT(customer_id) |
+--------------+--------------------+
| 2010-05-01 | 0 |
| 2010-05-02 | 0 |
| 2010-05-03 | 1 |
| 2010-05-04 | 0 |
| 2010-05-05 | 0 |
+--------------+--------------------+
是否可以实现这种行为？
I'm trying to create SELECT statement with a GROUP BY clause, which should return "default values".
Imagine the following simple MySQL table:
CREATE TABLE `tracker` (
`id` INTEGER PRIMARY KEY auto_increment,
`date` DATETIME NOT NULL,
`customer_id` INTEGER NOT NULL
);
The table contains only one record:
INSERT INTO `tracker` (`date`, `customer_id`) VALUES('2010-05-03', 1);
After wards I'm executing the following SQL query:
SELECT DATE(`date`), COUNT(customer_id) FROM tracker
WHERE DATE(`date`) >= '2010-05-01' AND DATE(`date`) <= '2010-05-05'
GROUP BY DATE(`date`) ORDER BY DATE(`date`);
And get the expected result set:
+----+---------------------+-------------+
| id | date | customer_id |
+----+---------------------+-------------+
| 1 | 2010-05-10 00:00:00 | 1 |
+----+---------------------+-------------+
However, I would like the result set to look like this:
+--------------+--------------------+
| DATE(`date`) | COUNT(customer_id) |
+--------------+--------------------+
| 2010-05-01 | 0 |
| 2010-05-02 | 0 |
| 2010-05-03 | 1 |
| 2010-05-04 | 0 |
| 2010-05-05 | 0 |
+--------------+--------------------+
Is it possible to achieve this behavior?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以构建一个范围内有效日期的临时表，然后将其合并到您的查询中 - 这是我可以立即看到的唯一前进方法......
马丁
You could build a temporary table of the valid dates in the range and then incorporate that into your query - that's about the only way forward that I can immediately see...
Martin
正如马丁所说，最好的解决方案是创建一个包含日期的临时表。
然后有 2 种方法：
与该临时表执行外连接并对结果执行
group by，或者group by+UNION select date,0 作为 date_table d 中不存在的计数（从客户 c 中选择 1，其中c.date=d.date)As Martin said, the best solution is to create a temp table with dates.
Then there's 2 approaches:
Do an outer join with that temp table and do a
group byon result, ORgroup byon the original table +UNION select date,0 as count from date_table d where not exists (select 1 from customer c where c.date=d.date)