网站建设工作量评估,wordpress停用谷歌字体,wordpress 大型分离,学校学生网站模板下载1.所有有交集的区间 场景#xff1a;在事件表里查找某年员工的岗位系数#xff0c;并计算其加权平均数。case1#xff1a;该员工是老员工#xff0c;从2020年一直到2049年。case2#xff1a;该员工是老员工#xff0c;但是今年离职。case3#xff1a;该员工是今年的新员…1.所有有交集的区间 场景在事件表里查找某年员工的岗位系数并计算其加权平均数。case1该员工是老员工从2020年一直到2049年。case2该员工是老员工但是今年离职。case3该员工是今年的新员工今年入职今年离职。case4该员工是今年的新员工一直在职。case5:该员工是今年的新员工未来离职。
select * from A where datediff(MINUTE,FromDate,CONVERT(CHAR(5),{TERM},120)12-31)0 and datediff(MINUTE ,CONVERT(CHAR(5),{TERM},120)01-01 ,ToDate )0 2.部分有交集 场景 select * from A where ((datediff(MINUTE,begintime,endtime)0 and datediff(MINUTE ,BeginTime ,BeginTime )0)or ( datediff(MINUTE,EndTime ,endtime)0 and datediff(MINUTE ,BeginTime ,EndTime )0 ))3.只有子集 场景 select * from A where BeginTimeBeginTime And EndTimeEndTime4.没交集 场景 select * from A Where BeginTimea.EndTimeOr BeginTimeEndTime5.取交集外的部分 场景 Select Case When b.BeginTime Between a.BeginTime And a.EndTime And b.EndTimea.EndTime Then a.BeginTime Else b.EndTime End,Case When a.BeginTime Between b.BeginTime And b.EndTime And a.EndTimeb.EndTime Then a.EndTime Else b.BeginTime End
from A a,B b Where (a.BeginTimeb.BeginTime Or a.EndTimeb.EndTime)And ((b.BeginTime Between a.BeginTime And a.EndTime And b.EndTimea.EndTime)Or (a.BeginTime Between b.BeginTime And b.EndTime And a.EndTimeb.EndTime))And Case When b.BeginTime Between a.BeginTime And a.EndTime And (b.EndTimea.EndTime) Then a.BeginTime Else b.EndTime EndCase When a.BeginTime Between b.BeginTime And b.EndTime And (a.EndTimeb.EndTime) Then a.EndTime Else b.BeginTime End6.取交集部分 场景 Select Case When b.BeginTime Between a.BeginTime And a.EndTime And b.EndTimea.EndTime Then b.begintime Else a.begintime End,Case When a.BeginTime Between b.BeginTime And b.EndTime And a.EndTimeb.EndTime Then a.endtime Else b.endtime End
From A a,B b
Where (a.BeginTimeb.BeginTime Or a.EndTimeb.EndTime)And ((b.BeginTime Between a.BeginTime And a.EndTime And b.EndTimea.EndTime)Or (a.BeginTime Between b.BeginTime And b.EndTime And a.EndTimeb.EndTime))And Case When b.BeginTime Between a.BeginTime And a.EndTime And (b.EndTimea.EndTime) Then a.BeginTime Else b.EndTime EndCase When a.BeginTime Between b.BeginTime And b.EndTime And (a.EndTimeb.EndTime) Then a.EndTime Else b.BeginTime End7.一般的取交集左端都要取最大右端取最小 场景
Select Case When BeginDateBeginDate Then BeginDate Else BeginDate End, Case When EndDateEndDate Then EndDate Else EndDate End
