SELECT
DATE_FORMAT( datetime列, '%Y-%m-%d' ) AS 日期,
SUM( ... ) AS 合计,
COUNT( ... ) AS 行数
FROM
表
GROUP BY
DATE_FORMAT( datetime列, '%Y-%m-%d' )
追问这样按时间分组查的话,如果其中有一天没有记录的话,就不会出现在结果集里了吧,现在是要每一天都要显示,没有记录的那一天就要显示0,这改怎么写?
追答CREATE TABLE test12 (
test_date datetime,
test_amt INT
);
INSERT INTO test12 VALUES ( '2013-01-01 12:00:00', 1);
INSERT INTO test12 VALUES ( '2013-01-03 12:00:00', 3);
INSERT INTO test12 VALUES ( '2013-01-05 12:00:00', 5);
INSERT INTO test12 VALUES ( '2013-01-07 12:00:00', 7);
INSERT INTO test12 VALUES ( '2013-01-09 12:00:00', 9);
INSERT INTO test12 VALUES ( '2013-01-11 12:00:00', 11);
INSERT INTO test12 VALUES ( '2013-01-13 12:00:00', 13);
INSERT INTO test12 VALUES ( '2013-01-15 12:00:00', 15);
SELECT
t2.all_day AS `日期`,
IFNULL(SUM( test_amt ), 0) AS `合计`,
COUNT( test_amt ) AS `行数`
FROM
(
SELECT
@rownum:=@rownum+1 AS NO,
DATE_ADD( '2013-01-01', INTERVAL @rownum DAY) AS all_day
FROM
(SELECT @rownum:=-1) r,
test12
) t2
LEFT JOIN test12 ON ( t2.all_day = DATE(test12.test_date) )
WHERE
t2.all_day >= '2013-01-01'
AND t2.all_day <= '2013-01-06'
GROUP BY
t2.all_day;
查询结果由于字数限制, 贴不出来了。
结果有6行, 1号到6号, 其中, 1,3,5 号有数据, 2,4,6号数值为0
本回答被提问者采纳