複雜sql處理邏輯 case when的用法

場景：

實習計劃中，包括輪換計劃，個人計劃。輪換計劃之間不會存在時間疊加。個人計劃於輪換計劃之間可能會存在時間疊加。疊加部分以個人計劃為準。計劃狀態包括：未開始，實習中，實習中斷，實習結束。 狀態根據開始時間，結束時間與當前時間的關係來計算。但是需要通過sql處理，疊加狀態時間判斷。

圖例：

假如當前系統時間是2019-11-26，序號3的狀態應該為實習中，序號4的狀態應該為實習中斷。

假如當前系統時間為2019-11-29，序號2的狀態是實習中，序號3的狀態是實習結束，序號4的狀態是實習中斷。

假如當前系統時間為2019-12-01，序號2的狀態是實習結束，序號3的狀態是實習結束，序號4的狀態是實習中。

說明：

1、當是個人計劃時，只要當前系統時間是處於該計劃的範圍內，該計劃就是實習中。（比如圖一、圖二）

2、當是輪換計劃時，要分時間考慮。如果該時間段內，且是當前系統時間所處的時間範圍，沒有其他個人計劃時，該輪換計劃就是實習中。（比如上圖三）而改時間段內，是當前系統時間所處時間範圍，有其他個人計劃時，該輪換計劃就是實習中斷。（比如上圖一、圖二）

背景：

後臺採用三個表trainee_info 實習生表 trainee_rotation_program 計劃表 trainee_group_info 輪換分組表

計劃表中存在 trainee_id 實習生id 、group_id 分組id 、plan_type 計劃類型

計劃類型為1或者為null的是輪換計劃，關聯分組id 計劃類型為2的是個人計劃，關聯實習生id

資料庫保存的時間格式是 年月日 時分秒的 但是時分秒全都是0 比如2019-11-26 00:00:000

所以寫sql判斷時間時要注意時分秒的處理

sql：

select a.name,c.structure_name as dept_name,b.starttime,b.endtime, ( <!-- 第一種情況 個人計劃 只要當前時間在範圍內 優先個人計劃 個人計劃實習中--> <!-- 第二種情況 輪科計劃 當前時間在範圍內，且當前時間 存在其他個人計劃優先於輪科計劃 輪科計劃 實習中斷 --> <!-- 第三種情況 輪科計劃 當前時間在範圍內，且當前時間 不存在其他個人計劃優先輪科計劃的情況 輪科計劃 實習中 --> <!-- 第四種情況 任何計劃 當前時間小於開始時間 未開始--> <!-- 第五種情況 任何計劃 當前時間大於結束時間 實習結束--> <![CDATA[ case when b.plan_type='2' and DATEADD(DAY,1,b.endtime) > GETDATE() and GETDATE() > b.starttime then 1 when (b.plan_type is null or b.plan_type='1') and DATEADD(DAY,1,b.endtime) > GETDATE() and GETDATE() > b.starttime and ( select count(1) from ( select * from trainee_rotation_program t where (t.trainee_id =a.id) and ( (b.starttime <= t.starttime and t.starttime <= b.endtime) or (t.endtime >= b.starttime and t.endtime <= b.endtime) or (t.starttime >= b.starttime and t.endtime <= b.endtime) ) and (DATEADD(DAY,1,t.endtime) > GETDATE() and GETDATE() > t.starttime) ) aa )>0 then 3 when (b.plan_type is null or b.plan_type='1') and DATEADD(DAY,1,b.endtime) > GETDATE() and GETDATE() > b.starttime and ( select count(1) from ( select * from trainee_rotation_program t where (t.trainee_id =a.id) and ( (b.starttime <= t.starttime and t.starttime <= b.endtime) or (t.endtime >= b.starttime and t.endtime <= b.endtime) or (t.starttime >= b.starttime and t.endtime <= b.endtime) ) and (DATEADD(DAY,1,t.endtime) > GETDATE() and GETDATE() > t.starttime) ) aa )=0 then 1 when GETDATE() < b.starttime then 0 when GETDATE() > DATEADD(DAY,1,b.endtime) then 2 else 2 end ]]> ) curstate, a.starttime as userstar,a.endtime as userend,b.plan_type as plan_type, (case when (b.plan_type=1 or b.plan_type is null) then '輪換計劃('+g.name+')' when b.plan_type=2 then '個人計劃' else '' end) as planSource, orderid from trainee_info a LEFT JOIN trainee_rotation_program b on (a.group_id = b.group_id or b.trainee_id=a.id) left join trainee_group_info g on b.group_id = g.id LEFT JOIN org_structure c on b.dept_id=c.id where a.id=#{id} and b.endtime>a.starttime order by orderid,b.starttime desc <!-- 先根據排序號排序，再根據開始時間排序，用於個人計劃的插單排序 -->查詢sql結果：（id=63）

關鍵部分解析以及思路：

1、由於優先個人計劃，先判斷個人計劃類型，以及當前系統時間在範圍內，注意時分秒的處理，返回1表示進行中。

2、判斷輪換計劃，實習中斷的情況。首先類型是輪換計劃，再篩選是否存在同時間段內，符合條件的個人計劃 如果存在，則說明是實習中斷狀態。返回3表示實習中斷。

判斷條件包括 個人計劃；開始時間在範圍中，或者結束時間在範圍中，或者開始時間結束時間都在範圍中；當前系統是否處於該範圍。

以圖一為例 使用case when 判斷序號4的狀態 序號4的開始時間為 2019-04-01 結束時間為 2020-01-02 當前系統時間為2019-11-26

上述系統中，存在一條序號3的記錄滿足判斷條件 序號3的開始時間為 2019-11-26 結束時間為 2019-11-28 屬於開始時間結束時間都處於序號4的範圍內，且當前系統時間也處於序號3的 範圍內

3、判斷輪換計劃，實習中的情況。首先類型是輪換計劃，再篩選2中相同的條件，如果不存在記錄，則說明是實習中。返回1表示實習中。

以圖三為例 使用case when 判斷序號4的狀態 序號4的開始時間為 2019-04-01 結束時間為 2020-01-02 當前系統時間為2019-12-01

上述系統中，存在序號2 序號3滿足時間範圍的條件，2019-11-26到2019-11-28 2019-11-29到2019-11-30 都屬於開始時間結束時間都處於序號4的範圍內，但不滿足當前系統時間範圍的條件

4、判斷任何計劃，只要當前系統時間比開始時間小，都返回0，表示未開始。

5、判斷任何計劃，只要當前系統時間比結束時間大，都返回2，表示實習結束。