mysql

---

1、if用法

select if(字段名 == 条件,满足时，不满足时) from table
select if(字段名 is null ,满足时，不满足时) from table

2、将某字段以逗号分隔并取前n个

select SUBSTRING_INDEX(字段名,‘,’,n) from table

3、将多行的值合并并以逗号分隔

select GROUP_CONCAT(SUBSTRING_INDEX(g.picture_url,‘,’,1) SEPARATOR ‘,’)

4、根据起止日期计算天数

select ABS(DATEDIFF(start_time,end_time)) + 1 AS cycle from table

5、按天分组统计，无数据显示0

首先准备一张表table(注意table中的数据一定要足够多，比如想要查一个月内的分组统计，table表中就一定要有30条记录)，@cdate, INTERVAL - 1 DAY中的DAY可替换成MONTH和YEAR。
SELECT
@cdate := DATE_ADD( @cdate, INTERVAL - 1 DAY ) DAY
FROM
( SELECT @cdate := DATE_ADD( ‘2023-06-13’, INTERVAL + 1 DAY ) FROM table) t0
LIMIT 15;

之后进行关联查询
三个问号分别填入结束日期，查询的总天数，自定义查询条件
select count(b.id) count,DATE_FORMAT(a.upload_time, ‘%Y-%m-%d’) uploadTime from ( SELECT @cdate := DATE_ADD( @cdate, INTERVAL - 1 DAY ) upload_time FROM ( SELECT @cdate := DATE_ADD( ?, INTERVAL + 1 DAY ) FROM gas_upload_alarm_data_residents ) t0 LIMIT ?) a left join (select * from gas_upload_alarm_data_residents where device_id = ?) b on a.upload_time = DATE_FORMAT(b.upload_time, ‘%Y-%m-%d’) GROUP BY a.upload_time