可以通过纯SQL查询完成此操作,还是必须使用某些PHP逻辑?
是的,最好创建一个Numbers表(单列N),该表只包含数字0到999。它可以用于很多事情,尤其是如下查询:
SELECT COUNT(t.click_date) as clicks,
DATE_FORMAT(adddate($start_date, interval N day), '%d %M %Y') as point
FROM Numbers
LEFT JOIN tracking t
ON t.click_date >= adddate($start_date, interval N day)
and t.click_date < adddate($start_date, interval (N+1) day)
WHERE N between 0 and datediff($start_date, $end_date)
GROUP BY N
顺便说一句,为什么我将2011年2月0日作为第一次约会
您使用的格式错误。W的大写W在一周中的某天不降低,因此对于月中的 或 。@L_403_0@http://dev.mysql.com/doc/refman/5.5/zh-CN/date-and-time- functions.html#function_date- format
也许我的GROUP BY无法正常工作?
您使用的GROUP BY DAY(FROM_UNIXTIME(click_date))
是 而不是工作日的注释,但您正在显示(或试图显示) (工作日)-选择一个,不要混合使用。
如果您不希望具体化(创建为真实表)数字序列表,则可以动态构建一个。不会很漂亮
注意:下面的N1,N2和N3组合起来可能会产生0-999的范围
SELECT COUNT(t.click_date) as clicks,
DATE_FORMAT(adddate($start_date, interval N day), '%d %M %Y') as point
FROM (
select N1 * 100 + N2 * 10 + N3 as N
from (
select 0 N1 union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all
select 8 union all select 9) N1
cross join (
select 0 N2 union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all
select 8 union all select 9) N2
cross join (
select 0 N3 union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all
select 8 union all select 9) N3
) Numbers
LEFT JOIN tracking t
ON t.click_date >= adddate($start_date, interval N day)
and t.click_date < adddate($start_date, interval (N+1) day)
WHERE N between 0 and datediff($start_date, $end_date)
GROUP BY N
一个直接的日期表
将其放在PHPMyAdmin的新窗口中或批量运行。它创建一个名为Dates的表,其中每个日期都是从天1900-01-01
(或脚本中的更改)到2300-01-01
(或更改)的每个日期。
DROP PROCEDURE IF EXISTS FillDateTable;
delimiter //
CREATE PROCEDURE FillDateTable()
LANGUAGE sql
NOT DETERMINISTIC
CONTAINS sql
sql Security DEFINER
COMMENT ''
BEGIN
drop table if exists datetable;
create table datetable (thedate datetime primary key, isweekday smallint);
SET @x := date('1900-01-01');
REPEAT
insert into datetable (thedate, isweekday) SELECT @x, case when dayofweek(@x) in (1,7) then 0 else 1 end;
SET @x := date_add(@x, interval 1 day);
UNTIL @x > date('2300-01-01') END REPEAT;
END//
delimiter ;
CALL FillDateTable;
有了这样的实用程序表,您的查询就可以
SELECT COUNT(t.click_date) as clicks,
DATE_FORMAT(thedate, '%d %M %Y') as point
FROM Dates
LEFT JOIN tracking t
ON t.click_date >= thedate
and t.click_date < adddate(thedate, interval 1 day)
WHERE thedate between $start_date and $end_date
GROUP BY thedate