LeetCode（数据库）- 报告系统状态的连续日期

--解决方案(1)
SELECTperiod_state, MIN(date) ASstart_date, MAX(date) ASend_dateFROM( 
SELECTfail_dateASdate, 
'failed'ASperiod_state,
IF(DATEDIFF(@pre, @pre :=fail_date) =-1, @id, @id :=@id+1) ASidFROMFaileda1, (SELECT@id :=0, @pre :=NULL) b1UNIONALLSELECTsuccess_dateASdate, 
'succeeded'ASperiod_state,
IF(DATEDIFF(@pre, @pre :=success_date) =-1, @id, @id :=@id+1) ASidFROMSucceededa2, (SELECT@id :=0, @pre :=NULL) b2) t3WHEREdateBETWEEN"2019-01-01"AND"2019-12-31"GROUPBYt3.idORDERBYstart_date--解决方案(2)
WITHt1AS(SELECTCAST(@num :=@num+IF(@preISNOTNULLANDDATEDIFF(success_date, @pre) =1, 0, 1) ASSIGNED) rk, @pre :=success_datedt, 1markFROMSucceeded, (SELECT@pre:=null, @num:=0) initWHEREsuccess_dateBETWEEN'2019-01-01'AND'2019-12-31'),
t2AS(SELECTrs2.minndt1, rs1.maxndt2, rs1.rk, rs1.markFROM(SELECTMAX(dt) maxn, rk, markFROMt1GROUPBYrk) rs1JOIN(SELECTMIN(dt) minn, rk, markFROMt1GROUPBYrk) rs2ONrs1.rk=rs2.rk),
t3AS(SELECTCAST(@num :=@num+IF(@preISNOTNULLANDDATEDIFF(fail_date, @pre) =1, 0, 1) ASSIGNED) rk, @pre :=fail_datedt, 0markFROMFailed, (SELECT@pre:=null, @num:=0) initWHEREfail_dateBETWEEN'2019-01-01'AND'2019-12-31'),
t4AS(SELECTrs2.minndt1, rs1.maxndt2, rs1.rk, rs1.markFROM(SELECTMAX(dt) maxn, rk, markFROMt3GROUPBYrk) rs1JOIN(SELECTMIN(dt) minn, rk, markFROMt3GROUPBYrk) rs2ONrs1.rk=rs2.rk)
SELECTIF(mark=0, 'failed', 'succeeded') period_state, dt1start_date, dt2end_dateFROM (SELECT*FROMt2UNIONALLSELECT*FROMt4) RSORDERBYdt1