Question

我的表格有两栏、日期和记分。在30个条目中,每30天有一个条目。

date      score
-----------------
1.8.2010  19
2.8.2010  21
4.8.2010  14
7.8.2010  10
10.8.2010 14

我的问题是一些日期缺失——我希望看到:

date      score
-----------------
1.8.2010  19
2.8.2010  21
3.8.2010  0
4.8.2010  14
5.8.2010  0
6.8.2010  0
7.8.2010  10
...

我从单一询问中需要的是:19,21,9,14,00,10,0,0,14...... 这意味着,缺失的日期是零。

我知道如何获得所有价值,用服务器的辅助语言,通过日期和缺失的带子。但是,这是用我方言做的,因此,我可以按日期对结果进行分类,拿到失踪的物品。

EDIT:本表还有另一个名为用户信息数据库的栏目,因此,我有30 000名用户,其中一些人在本表中拥有分数。我删除日期和日期;30天前,因为我需要每个用户最后30天记分。原因是,我正在绘制过去30天用户活动的图表,并绘制图一,需要按 com子分列的30个数值。因此,在问询中,我可以说,美国电离层电离层电离层电离层电离层电离层电离层10203年活动,而电离层将给我30分,过去30天每个点一个。我希望,我现在更加清楚。

Answer 1

MySQL确实有可复读功能,因此,你再使用NUMBERS表的trick计。

a. 设立一个表格,仅保留增量数字——易于使用汽车增量:

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;

利用:

INSERT INTO `example`.`numbers`
  ( `id` )
VALUES
  ( NULL )

......你们所需要的价值太多。

使用DATE_ADD,以制定日期清单,根据NUMBERS.id数值增加天数。将“2010-06”和“2010-06-14”改为“2010-06-14”(但采用相同的格式,YYY-MM-DD) -

SELECT `x`.*
  FROM (SELECT DATE_ADD( 2010-06-06 , INTERVAL `n`.`id` - 1 DAY)
          FROM `numbers` `n`
         WHERE DATE_ADD( 2010-06-06 , INTERVAL `n`.`id` -1 DAY) <=  2010-06-14  ) x

LEFT JOIN在根据时间段排列的数据表中:

   SELECT `x`.`ts` AS `timestamp`,
          COALESCE(`y`.`score`, 0) AS `cnt`
     FROM (SELECT DATE_FORMAT(DATE_ADD( 2010-06-06 , INTERVAL `n`.`id` - 1 DAY),  %m/%d/%Y ) AS `ts`
             FROM `numbers` `n`
            WHERE DATE_ADD( 2010-06-06 , INTERVAL `n`.`id` - 1 DAY) <=  2010-06-14 ) x
LEFT JOIN TABLE `y` ON STR_TO_DATE(`y`.`date`,  %d.%m.%Y ) = `x`.`ts`

如果你想要保持日期格式,则使用D_FORMAT=:

DATE_FORMAT(`x`.`ts`,  %d.%m.%Y ) AS `timestamp`

Answer 2

I m 不是其他答复的狂热,需要编制表格。如果没有帮助表,这种询问是有效的。

SELECT 
    IF(score IS NULL, 0, score) AS score,
    b.Days AS date
FROM 
    (SELECT a.Days 
    FROM (
        SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
        FROM       (SELECT 0 AS a 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) AS a
        CROSS JOIN (SELECT 0 AS a 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) AS b
        CROSS JOIN (SELECT 0 AS a 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) AS c
    ) a
    WHERE a.Days >= curdate() - INTERVAL 30 DAY) b
LEFT JOIN your_table
    ON date = b.Days
ORDER BY b.Days;

因此,不要这样做。

SELECT 
    IF(score IS NULL, 0, score) AS score,
    b.Days AS date

如果有的话,就会发现没有记分的日子,将其定为0天。 b.Days是你选择从现在起到1 000天的组合天数。

    (SELECT a.Days 
    FROM (
        SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
        FROM       (SELECT 0 AS a 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) AS a
        CROSS JOIN (SELECT 0 AS a 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) AS b
        CROSS JOIN (SELECT 0 AS a 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) AS c
    ) a
    WHERE a.Days >= curdate() - INTERVAL 30 DAY) b

这种分局是我所看到的排位。从现在起,它有效地编制了过去1 000天的清单。最终的WHERE条款中的间隔期(目前为30天)决定了返回的天数;最长为1,000天。这个问题可以很容易地修改,以恢复100年时间,但10 000年多数情况都是好的。

LEFT JOIN your_table
    ON date = b.Days
ORDER BY b.Days;

这是你把记分列入表格的部分。与选定日期相比,从产生日期的查询到必要时能够填写0份(分数将定在NUL,最初是LEFT JOIN;这在选定的说明中确定)。我也定在日期之前,原因就是如此。这是一种偏好,你也可以按分数排列。

在ORDER BY之前,您可以很容易地加入您关于用户与您的编辑提到的表格,以补充最后的要求。

我希望这一说法有助于人。感谢阅读。

Answer 3

自提出这一问题以来,时间已经过去。 MySQL 8.0于2018年获释,并为提供了权威、最先进的解决这一问题的办法。

以下询问可用于编制2010年8月头15天的日期清单:

with recursive all_dates(dt) as (
    -- anchor
    select  2010-08-01  dt
    union all 
    -- recursion with stop condition
    select dt + interval 1 day from all_dates where dt <  2010-08-15 
)
select * from all_dates order by dt

届时,您可加入。

with recursive all_dates(dt) as (
    select  2010-08-01  dt
    union all 
    select dt + interval 1 day from all_dates where dt <  2010-08-15 
)
select d.dt date, coalesce(t.score, 0) score
from all_dates d
left join mytable t on t.date = d.dt
order by d.dt

<https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2e1afb7a2286a9c0c219e3f31de2ef49”rel=“nofollow noreferer”>Demo on DB Fiddle:

date       | score
:--------- | ----:
2010-08-01 |    19
2010-08-02 |    21
2010-08-03 |     0
2010-08-04 |    14
2010-08-05 |     0
2010-08-06 |     0
2010-08-07 |    10
2010-08-08 |     0
2010-08-09 |     0
2010-08-10 |    14
2010-08-11 |     0
2010-08-12 |     0
2010-08-13 |     0
2010-08-14 |     0
2010-08-15 |     0

请注意,在其他间隔期或期间调整补习计算非常容易。举例来说,我们要说,2010年8月1日,每15分钟4 AM至8 AM;我们可以:

with recursive all_dates(dt) as (
    select  2010-08-01 04:00:00  dt
    union all 
    select dt + interval 15 minute from all_dates where dt <  2010-08-01 08:00:00 
)
...

Answer 4

您可以通过Calendar Table来做到这一点。这张表格是你制作的,填写了日期范围(例如,2000-2050年每日数据集;这取决于你的数据)。然后,你可以就日历表外加一席。如果在您的桌子上遗漏了日期,你将分回0。

Answer 5

Michael Conard的答复是巨大的,但我需要15分钟的间隔,时间必须总是在每15分钟顶点开始:

SELECT a.Days 
FROM (
    SELECT FROM_UNIXTIME( FLOOR( UNIX_TIMESTAMP() / (15 * 60) ) * (15 * 60)) - INTERVAL 15 * (a.a + (10 * b.a) + (100 * c.a)) MINUTE AS Days
    FROM       (SELECT 0 AS a 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) AS a
    CROSS JOIN (SELECT 0 AS a 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) AS b
    CROSS JOIN (SELECT 0 AS a 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) AS c
) a
WHERE a.Days >= curdate() - INTERVAL 30 DAY

这将使目前时间达到上一轮第15分钟:

FROM_UNIXTIME( FLOOR( UNIX_TIMESTAMP() / (15 * 60) ) * (15 * 60))

这将用15分钟的步走:

- INTERVAL 15 * (a.a + (10 * b.a) + (100 * c.a)) MINUTE

如果采取更简单的方式,请让我知道。

Answer 6

您可以直接使用,从开始到今天,插入

        with recursive all_dates(dt) as (
        -- anchor
        select  2021-01-01  dt
            union all 
        -- recursion with stop condition
        INSERT IGNORE  INTO mytable (date,score) VALUES (dt + interval 1 day ,0 )  where dt + interval 1 day <= curdate()
    )
    select * from all_dates

友情链接