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

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

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

SELECT
	* 
FROM
	(
	SELECT
		LV 
	FROM
		( SELECT LEVEL LV FROM DUAL CONNECT BY 100 >= LEVEL ) C1 
	WHERE
		C1.LV > 12 
		AND c1.lv <> 36 
	) C
	JOIN (
	SELECT
		12 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 or
		DATE '2021-01-01' AS STARTDATE,
		DATE '2022-01-01' AS ENDDATE 
	FROM
		DUAL UNION
	SELECT
		12 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 
	FROM
		DUAL 
		) 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的数据是从数据库中查询出来的数据，如果查询结果有数据，则证明有重复，否则没有重复。