校验数据是否重叠（各种操作符＞,＜,＞=,＜=,or,and）

最近接到一个需求，其中部分功能涉及到数据的重叠校验，并且录入的数据需要包含各种操作符。如果只通过java代码来查询并进行循环判断的话，判断情况会很复杂，幸好有同事的帮忙提供了一个用sql查询重叠部分的方法，现在分享一下供大家参考

 1.首先先具体介绍一下需求，主要是根据开始时间和结束时间来查询 相同时间段内是否有期限重叠的数据，如果有重叠则不让添加。也就是时间段和期限都重叠的数据不允许添加。

SELECT* 
FROM(SELECTLV FROM( SELECT LEVEL LV FROM DUAL CONNECT BY 100 >= LEVEL ) C1 WHEREC1.LV > 12 AND c1.lv <> 36 ) CJOIN (SELECT12 AS A,1 AS AA,--1 > 2>= 3< 4 <= 5 <>24 AS B,3 AS BB,--1 > 2>= 3< 4 <= 5 <>1 AS C,-- 1 and 2 orDATE '2021-01-01' AS STARTDATE,DATE '2022-01-01' AS ENDDATE FROMDUAL UNIONSELECT12 AS A,1 AS AA,--1 > 2>= 3< 4 <= 5 <>36 AS B,3 AS BB,--1 > 2>= 3< 4 <= 5 <>1 AS C,DATE '2022-01-01' AS STARTDATE,DATE '2023-01-01' AS ENDDATE FROMDUAL ) D ON (D.C = 1 AND ((( D.AA = 1 AND LV > D.A ) OR ( D.AA = 2 AND LV >= D.A ) OR ( D.AA = 3 AND LV < D.A ) OR ( D.AA = 4 AND LV <= D.A ) OR ( D.AA = 5 AND LV <> D.A ) ) AND (( D.BB = 1 AND LV > D.B ) OR ( D.BB = 2 AND LV >= D.B ) OR ( D.BB = 3 AND LV < D.B ) OR ( D.BB = 4 AND LV <= D.B ) OR ( D.BB = 5 AND LV <> D.B ) ) ) ) OR ((D.C = 2 AND ((( D.AA = 1 AND LV > D.A ) OR ( D.AA = 2 AND LV >= D.A ) OR ( D.AA = 3 AND LV < D.A ) OR ( D.AA = 4 AND LV <= D.A ) OR ( D.AA = 5 AND LV <> D.A ) ) OR (( D.BB = 1 AND LV > D.B ) OR ( D.BB = 2 AND LV >= D.B ) OR ( D.BB = 3 AND LV < D.B ) OR ( D.BB = 4 AND LV <= D.B ) OR ( D.BB = 5 AND LV <> D.B ) ) ) ) )

其中 SELECT LEVEL LV FROM DUAL CONNECT BY 100 >= LEVEL 是用来限定期限的数据，根据自己的具体情况来确认数据范围。我这里用100就足够了，其中的条件就是我们录入的数据。join的数据是从数据库中查询出来的数据，如果查询结果有数据，则证明有重复，否则没有重复。