超实用的SQL语法2

最新推荐文章于 2024-09-20 21:26:51 发布

孤芳克格勃C.L

最新推荐文章于 2024-09-20 21:26:51 发布

阅读量1.8k

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_44005694/article/details/112820670

版权

查询最近前七天(含当天)的日期集合

SELECT curdate() as click_date
    union all
    SELECT date_sub(curdate(), interval 1 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 2 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 3 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 4 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 5 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 6 day) as click_date

查询未来七天(不含当天)的日期集合

select DATE_SUB(curdate(),INTERVAL -1 DAY) day1 union
select DATE_SUB(curdate(),INTERVAL -2 DAY) day2 union
select DATE_SUB(curdate(),INTERVAL -3 DAY) day3 union
select DATE_SUB(curdate(),INTERVAL -4 DAY) day4 union
select DATE_SUB(curdate(),INTERVAL -5 DAY) day5 union
select DATE_SUB(curdate(),INTERVAL -6 DAY) day6 union
select DATE_SUB(curdate(),INTERVAL -7 DAY) day7

根据查询前七天日期集合，检索相关表数据记录

select a.click_date,ifnull(b.count,0) as count
from (
    SELECT curdate() as click_date
    union all
    SELECT date_sub(curdate(), interval 1 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 2 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 3 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 4 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 5 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 6 day) as click_date
) a left join (
  select date(create_time) as datetime, count(*) as count
  from arms_task
  group by date(create_time)
) b on a.click_date = b.datetime;