参考：
mysql复杂sql书写示例
查询当天在某个小时之前每个小时的数据量
select DATE_FORMAT(create_time,'%H') hours,count(1) count from movie_item where date(create_time)=date(now()) group by hours
06 129
07 12
08 23
09 236
查询过去30天每天的数据量
select * from (select date(create_time) as day, count(1) as day_count from movie_item where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time) group by day desc
) k order by day
2024-07-16 872
2024-07-17 820
2024-07-18 766
2024-07-19 1030
2024-07-20 935
2024-07-21 1670
2024-07-22 1402
2024-07-23 1885
2024-07-24 1668
2024-07-25 1377
获取最近30天每天的总量和每天到当前小时+1为止的数据量
select date(create_time) as day, count(1) as day_count, '总量' as status from movie_item where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time) group by day union
select date(create_time) as day, count(1) as current_count, '迄今' as status from movie_item where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time) AND DATE_FORMAT(create_time, '%H') < DATE_FORMAT(now(),'%H') + 1 group by day
结果：
2024-07-16 872 总量
2024-07-17 820 总量
2024-07-18 766 总量
2024-07-19 1030 总量
2024-07-20 935 总量
2024-07-21 1670 总量
2024-07-22 1402 总量
2024-07-23 1885 总量
2024-07-24 1668 总量
2024-07-25 1377 总量
...
2024-08-08 1178 迄今
2024-08-09 891 迄今
2024-08-10 1524 迄今
2024-08-11 880 迄今
2024-08-12 593 迄今
2024-08-13 846 迄今
2024-08-14 1197 迄今
2024-08-15 1385 迄今
这种方式方便构建可视化的图，另一种更常见的查询方式是：
select current.day as day, current_count, day_count from(select date(create_time) as day, count(1) as current_count from movie_item where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time) AND DATE_FORMAT(create_time, '%H') < DATE_FORMAT(now(),'%H') + 1 group by day) current left join (select * from (select date(create_time) as day, count(1) as day_count from movie_item where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time) group by day desc
) k order by day ) daily on current.day=daily.day;day, current_count, day_count
2024-07-16 667 872
2024-07-17 458 820
2024-07-18 465 766
2024-07-19 660 1030
2024-07-20 539 935
2024-07-21 1015 1670
2024-07-22 1177 1402
2024-07-23 714 1885
2024-07-24 960 1668
2024-07-25 692 1377
...
这种方式更适合显示成表格。
使用IFNULL保障传参不合适的情况下仍然有默认结果输出
select DATE_FORMAT(create_time,'%H') hours,count(1) count from movie_item where date(create_time)=IFNULL(date(${days}), date(now())) group by hours由于在导入参数${days}时，会自动使用双引号包起来，所以即使传参为now()，也会变成"now()"，date(“now()”)=null，所以会调用后面的默认方法获取数据。
按预设顺序排序
select * from xxx where id in ( 618 , 619 , 1329 , 1336 , 1323 , 1324 , 1330 , 1331 , 1325 , 1326 ) order by FIELD(id , 618 , 619 , 1329 , 1336 , 1323 , 1324 , 1330 , 1331 , 1325 , 1326 );618 36092115
619 36810193
1329 6436754
1336 3037173
1323 1478186
1324 2301032
1330 25964242
1331 35043784
1325 2245704
1326 36289423
查询时使用case when映射状态到文本
select * from (select date(create_time) as day, (case s.spider_status when -1 then '失败' when 0 then '成功' end) as status, count(1) as count from spider_status s where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time) group by day desc, status desc ) k order by day2024-07-21 成功 366
2024-07-21 失败 37
2024-07-22 成功 457
2024-07-22 失败 84
2024-07-23 成功 843
2024-07-23 失败 107
2024-07-24 成功 577