如何使用mysql获取最近10天的记录

bxgwgixi 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(778)

我想检索过去10天记录的所有数据，但当我添加一个“where”时，它不能像预期的那样工作。
我的表结构：

CREATE TABLE `vip` (
  `id` int(11) NOT NULL auto_increment,
  `cancel_at_period_end` datetime NULL,
  `creation_date` datetime default NULL,
 `answer` varchar(5) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=499 ;

我使用了如下查询：

SELECT days.day, count(users_vip.id) count
FROM (SELECT curdate() as day 
                          union select curdate() - interval 1 day
                          union select curdate() - interval 2 day
                          union select curdate() - interval 3 day
                          union select curdate() - interval 4 day
                          union select curdate() - interval 5 day
                          union select curdate() - interval 6 day
                          union select curdate() - interval 7 day
                          union select curdate() - interval 8 day
                          union select curdate() - interval 9 day) days
LEFT JOIN users_vip on days.day = CONVERT(users_vip.creation_date, date)
WHERE users_vip.current_period_end_date >= NOW()
GROUP BY days.day

输出为：

date          count
2018-08-15    1

我想要的结果应该是这样的：

date          count
2018-08-08    0
2018-08-09    0
2018-08-10    0
2018-08-11    0
2018-08-12    0
2018-08-13    0
2018-08-14    0
2018-08-15    1
2018-08-16    0
2018-08-17    0

更新：

SELECT days.day, count(users_vip.id) count
FROM (SELECT curdate() as day 
                          union select curdate() - interval 1 day
                          union select curdate() - interval 2 day
                          union select curdate() - interval 3 day
                          union select curdate() - interval 4 day
                          union select curdate() - interval 5 day
                          union select curdate() - interval 6 day
                          union select curdate() - interval 7 day
                          union select curdate() - interval 8 day
                          union select curdate() - interval 9 day) days
LEFT JOIN users_vip on days.day = CONVERT(users_vip.creation_date, date) AND users_vip.current_period_end_date >= NOW()
GROUP BY days.day

mysql

来源：https://stackoverflow.com/questions/51897717/how-i-can-get-last-10-days-records-with-mysql

2条答案

按热度按时间

ac1kyiln1#

我认为您应该将日历表加入一个子查询，该子查询按天聚合计数：

SELECT
    days.day AS date,
    COALESCE(t.cnt, 0) AS count
FROM
(
    SELECT CURDATE() AS day UNION
    SELECT CURDATE() - INTERVAL 1 day UNION
    SELECT CURDATE() - INTERVAL 2 day UNION
    SELECT CURDATE() - INTERVAL 3 day UNION
    SELECT CURDATE() - INTERVAL 4 day UNION
    SELECT CURDATE() - INTERVAL 5 day UNION
    SELECT CURDATE() - INTERVAL 6 day UNION
    SELECT CURDATE() - INTERVAL 7 day UNION
    SELECT CURDATE() - INTERVAL 8 day UNION
    SELECT CURDATE() - INTERVAL 9 day
) days
LEFT JOIN
(
    SELECT DATE(creation_date) AS date, COUNT(*) AS cnt
    FROM users_vip
    WHERE current_period_end_date >= NOW()
    GROUP BY DATE(creation_date)
) t
    ON days.day = t.date;

赞(0）回复(0）举报 2021-06-20

juzqafwq2#

我们可以改变 WHERE 至 AND ，将 predicate 从 WHERE 条款 ON 条款。
我还避免使用函数 Package 器 creation_date ，以允许使用索引。我会重新编写匹配条件。
我会这样做：

SELECT days.day
     , COUNT(users_vip.id)     AS count
  FROM ( SELECT CURDATE() AS day 
         UNION ALL SELECT CURATE() + INTERVAL -1 DAY
         UNION ALL SELECT CURATE() + INTERVAL -2 DAY
         UNION ALL SELECT CURATE() + INTERVAL -3 DAY
         UNION ALL SELECT CURATE() + INTERVAL -4 DAY
         UNION ALL SELECT CURATE() + INTERVAL -5 DAY
         UNION ALL SELECT CURATE() + INTERVAL -6 DAY
         UNION ALL SELECT CURATE() + INTERVAL -7 DAY
         UNION ALL SELECT CURATE() + INTERVAL -8 DAY
         UNION ALL SELECT CURATE() + INTERVAL -9 DAY
       ) days
  LEFT
  JOIN users_vip
    ON users_vip.creation_date            >= days.day 
   AND users_vip.creation_date            <  days.day + INTERVAL 1 DAY
   AND users_vip.current_period_end_date  >= NOW() 
 GROUP
    BY days.day

赞(0）回复(0）举报 2021-06-20

我来回答

如何使用mysql获取最近10天的记录

2条答案

相关问题

热门标签

最新问答