企业官网建设流程全解析

1、建表

CREATETABLE`delivery_info`(`id`bigintNOTNULLAUTO_INCREMENT,`car_no`varchar(20)COLLATEutf8mb4_general_ciNOTNULL,`delivery_date`datetimeNOTNULL,`is_del`tinyint(1)DEFAULT'0',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_general_ci;

2、添加数据

INSERTINTO`test`.`delivery_info`(`id`,`car_no`,`delivery_date`,`is_del`)VALUES(1,'A','2023-01-01 11:11:11',0);INSERTINTO`test`.`delivery_info`(`id`,`car_no`,`delivery_date`,`is_del`)VALUES(2,'A','2023-01-01 12:12:11',0);INSERTINTO`test`.`delivery_info`(`id`,`car_no`,`delivery_date`,`is_del`)VALUES(3,'A','2023-01-02 11:44:44',0);INSERTINTO`test`.`delivery_info`(`id`,`car_no`,`delivery_date`,`is_del`)VALUES(4,'A','2023-01-05 11:44:44',0);INSERTINTO`test`.`delivery_info`(`id`,`car_no`,`delivery_date`,`is_del`)VALUES(5,'A','2023-01-06 11:44:55',0);INSERTINTO`test`.`delivery_info`(`id`,`car_no`,`delivery_date`,`is_del`)VALUES(6,'A','2023-02-01 00:14:51',0);INSERTINTO`test`.`delivery_info`(`id`,`car_no`,`delivery_date`,`is_del`)VALUES(7,'A','2023-02-02 12:51:56',0);INSERTINTO`test`.`delivery_info`(`id`,`car_no`,`delivery_date`,`is_del`)VALUES(8,'B','2023-01-01 12:20:20',0);INSERTINTO`test`.`delivery_info`(`id`,`car_no`,`delivery_date`,`is_del`)VALUES(9,'B','2023-01-02 02:02:02',0);INSERTINTO`test`.`delivery_info`(`id`,`car_no`,`delivery_date`,`is_del`)VALUES(10,'C','2025-02-01 11:51:41',0);

3、查询sql

SELECTgroup_id,car_no,count(1)FROM(SELECTdelivery_info.*,@group_id:=IF(@curr_car=car_noAND(DATEDIFF(DATE(delivery_date),@prev)=1ORDATEDIFF(DATE(delivery_date),@prev)=0),@group_id,DATE(delivery_date))ASgroup_id,@prev:=DATE(delivery_date),@curr_car:=car_noFROMdelivery_info,(SELECT@group_id:=0,@prev:=NULL,@curr_car:='')ASvarORDERBYcar_no,delivery_date)astemGROUPBYcar_no,group_id;

4、结果