1. 首页
  2. 问题
  3. 填补数据库返回的日期中的空白-可能使用纯SQL解决方案吗？
一尘不染

填补数据库返回的日期中的空白-可能使用纯SQL解决方案吗？

sql

我有这个查询：

SELECT COUNT(*) as clicks, DATE_FORMAT(FROM_UNIXTIME(click_date), '%w %M %Y') as point 
FROM tracking 
WHERE click_date < $end_date AND click_date > $start_date 
GROUP BY DAY(FROM_UNIXTIME(click_date))

$start_date两个星期前在哪里，$end_date今天是今天。

我正在尝试查找特定日期范围内每天的所有点击。我也想包括没有点击的日子。由于我的数据库中自然没有这些条目，因此我需要向他们包括一些方法，如何最好地做到这一点，同时显示从开始日期到结束日期的所有日期。这是我目前所拥有的，这两个星期的日期范围有很多差距。

Array
(
    [0] => Array
        (
            [clicks] => 17
            [point] => 0 February 2011
        )

    [1] => Array
        (
            [clicks] => 3
            [point] => 1 February 2011
        )

    [2] => Array
        (
            [clicks] => 14
            [point] => 5 February 2011
        )

    [3] => Array
        (
            [clicks] => 1
            [point] => 1 February 2011
        )

    [4] => Array
        (
            [clicks] => 8
            [point] => 2 February 2011
        )

)

可以通过纯SQL查询完成此操作，还是必须使用某些php逻辑？

顺便说一句，为什么我有0 February 2011第一次约会！嗯，我似乎也有重复的日期，这不应该发生，也许我GROUP BY的工作不正常吗？

谢谢大家的帮助。


阅读 137

收藏
2021-03-10

共1个答案

一尘不染

可以通过纯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在一周中的某天不降低，因此对于月中的 ‘％W％M％Y’ 或 ‘％d％M％Y’ 。
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))是 “ day”
而不是工作日的注释，但您正在显示（或试图显示） “％W” （工作日）-选择一个，不要混合使用。

编辑： 如果您不希望具体化（创建为真实表）数字序列表，则可以动态构建一个。不会很漂亮

注意：下面的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

编辑＃2： 一个直接的日期表

将其放在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
2021-03-10