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 day

2024-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