select会显示所有日期，即使您当天没有任何内容

7hiiyaii 于 2021-06-23 发布在 Mysql

关注(0)|答案(1)|浏览(254)

我的问题是：

SELECT DATE_FORMAT(post_date, '%Y-%m-%d') AS data_day
    , COUNT(*) AS count
FROM wpsa_posts
WHERE post_date >= DATE_ADD(CURDATE(),INTERVAL -7 DAY)
GROUP
    BY data_day
    ORDER BY data_day DESC
    LIMIT 7

查询结果如下：

如果我把最后30天

我想显示所有日期，如果我把7（出现在最后7天，如果没有在这一天出现计数0）
我想要的例子：我想要显示所有的日期，例如：

data_day     ||   count ||
2018-08-07   ||   0 if nothing on this day
2018-08-08   ||   32
2018-08-09   ||   1
2018-08-10   ||   4
2018-08-11   ||   0
2018-08-12   ||   0
2018-08-13   ||   0

我想表现得像那样

mysql wordpress mysql-workbench

来源：https://stackoverflow.com/questions/51725692/select-appears-all-dates-even-if-you-do-not-have-any-content-on-that-day

1条答案

按热度按时间

wlzqhblo1#

您已经生成了日期值并与您的表联接，然后就可以进行计数了

select date, count(post_date) from
(select  * from (
select 
 date_add('2018-07-17 00:00:00.000', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date 
  from
(select 0 as num
   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,
(select 0 as num
   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,
(select 0 as num
   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,
(select 0 as num
   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) n4,
(select 0 as num
   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) n5
) a
where date >'2018-07-17 00:00:00.000' and date < NOW()
) as t
left join wpsa_posts w on t.date=DATE_FORMAT(w.post_date, '%Y-%m-%d')
 WHERE date >= DATE_ADD(CURDATE(),INTERVAL -7 DAY)
 group by date

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

我来回答

select会显示所有日期，即使您当天没有任何内容

1条答案

相关问题

热门标签

最新问答