问题描述
我有一个查询,用于对表中的所有条目进行分组,并按datetime列进行分组。这一切都很好:
I have a query that groups all entries from a table and groups them by the datetime column. This is all working great:
SELECT SUM( `value` ) AS `sum` , DATE(`datetime`) AS `dt``
FROM `entry`
WHERE entryid = 85
AND DATETIME BETWEEN '2010-01-01' AND '2010-03-01'
GROUP BY `dt`
ORDER BY `datetime`
问题是,我需要它返回即使没有找到任何值,值设置为0。我假设有一些MYSQL函数会照顾这个但似乎找不到。
The problem is, I need it to return a row even if nothing is found, with the value set to 0. I assume there's some MYSQL function that'll take care of this but can't seem to find it.
谢谢!
推荐答案
MySQL没有递归功能,所以你使用NUMBERS表格技巧 -
MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -
-
创建一个只保存递增数字的表格 - 使用auto_increment方便使用:
Create a table that only holds incrementing numbers - easy to do using an auto_increment:
DROP TABLE IF EXISTS `example`.`numbers`;
CREATE TABLE `example`.`numbers` (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
使用以下方式填充表:
Populate the table using:
INSERT INTO NUMBERS
(id)
VALUES
(NULL)
...您需要的值越多。
...for as many values as you need.
使用构建日期列表,增加日期基于NUMBERS.id值。将2010-01-01和2010-03-01替换为您的开始和结束日期(但使用相同的格式,YYYY-MM-DD) -
Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value. Replace "2010-01-01" and "2010-03-01" with your respective start and end dates (but use the same format, YYYY-MM-DD) -
SELECT x.*
FROM (SELECT DATE_ADD('2010-01-01', INTERVAL n.id - 1 DAY)
FROM numbers n
WHERE DATE_ADD('2010-01-01', INTERVAL n.id -1 DAY) <= '2010-03-01' ) x
根据日期时间部分将LEFT JOIN加入到您的数据表上:
LEFT JOIN onto your table of data based on the datetime portion:
SELECT DATE(x.dt) AS dt,
COALESCE(SUM(e.value), 0) AS sum_value
FROM (SELECT DATE_ADD('2010-01-01', INTERVAL n.id - 1 DAY) AS dt
FROM numbers n
WHERE DATE_ADD('2010-01-01', INTERVAL n.id -1 DAY) <= '2010-03-01' ) x
LEFT JOIN ENTRY e ON DATE(e.datetime) = x.dt
AND e.entryid = 85
GROUP BY DATE(x.dt)
为什么数字而不是日期?
简单的日期可以根据数量生成,就像我提供的例子一样。这也意味着使用单个表,而每个数据类型表示一个。
Why Numbers, not Dates?
Simple - dates can be generated based on the number, like in the example I provided. It also means using a single table, vs say one per data type.
这篇关于按日期分组,即使没有找到记录,也返回行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!