1、建表CREATETABLEdelivery_info(idbigintNOTNULLAUTO_INCREMENT,car_novarchar(20)COLLATEutf8mb4_general_ciNOTNULL,delivery_datedatetimeNOTNULL,is_deltinyint(1)DEFAULT0,PRIMARYKEY(id))ENGINEInnoDBAUTO_INCREMENT11DEFAULTCHARSETutf8mb4COLLATEutf8mb4_general_ci;2、添加数据INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(1,A,2023-01-01 11:11:11,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(2,A,2023-01-01 12:12:11,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(3,A,2023-01-02 11:44:44,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(4,A,2023-01-05 11:44:44,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(5,A,2023-01-06 11:44:55,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(6,A,2023-02-01 00:14:51,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(7,A,2023-02-02 12:51:56,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(8,B,2023-01-01 12:20:20,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(9,B,2023-01-02 02:02:02,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(10,C,2025-02-01 11:51:41,0);3、查询sqlSELECTgroup_id,car_no,count(1)FROM(SELECTdelivery_info.*,group_id:IF(curr_carcar_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,(SELECTgroup_id:0,prev:NULL,curr_car:)ASvarORDERBYcar_no,delivery_date)astemGROUPBYcar_no,group_id;4、结果 mysql根据连续天数分组，计算连续天数
1、建表CREATETABLEdelivery_info(idbigintNOTNULLAUTO_INCREMENT,car_novarchar(20)COLLATEutf8mb4_general_ciNOTNULL,delivery_datedatetimeNOTNULL,is_deltinyint(1)DEFAULT0,PRIMARYKEY(id))ENGINEInnoDBAUTO_INCREMENT11DEFAULTCHARSETutf8mb4COLLATEutf8mb4_general_ci;2、添加数据INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(1,A,2023-01-01 11:11:11,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(2,A,2023-01-01 12:12:11,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(3,A,2023-01-02 11:44:44,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(4,A,2023-01-05 11:44:44,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(5,A,2023-01-06 11:44:55,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(6,A,2023-02-01 00:14:51,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(7,A,2023-02-02 12:51:56,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(8,B,2023-01-01 12:20:20,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(9,B,2023-01-02 02:02:02,0);INSERTINTOtest.delivery_info(id,car_no,delivery_date,is_del)VALUES(10,C,2025-02-01 11:51:41,0);3、查询sqlSELECTgroup_id,car_no,count(1)FROM(SELECTdelivery_info.*,group_id:IF(curr_carcar_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,(SELECTgroup_id:0,prev:NULL,curr_car:)ASvarORDERBYcar_no,delivery_date)astemGROUPBYcar_no,group_id;4、结果 RELATED READING
延伸阅读
更多一线实战笔记与深度复盘，助您持续精进
