SELECT * FROM table A WHERE A.begin_date IN (SELECT B.begin_date FROM table B GROUP BY B.begin_date HAVING COUNT(*)>1) AND (A.end_date-A.begin_date) = (SELE MIN(C.end_date-C.begin_date) FROM ( (SELECT D.begin_date FROM table D GROUP BY D.begin_date HAVING COUNT(*)>1) C )这个SQL应该能实现你要的效果!
------- 测试表 SQL> DESC TEST_T; Name Type Nullable Default Comments ---------- ---- -------- ------- -------- BEGIN_DATE DATE Y END_DATE DATE Y ------ 插入测试数据 BEGIN DELETE FROM TEST_T; COMMIT; FOR V_I IN 1 .. 20 LOOP INSERT INTO TEST_T VALUES (TRUNC(SYSDATE) - ROUND(DBMS_RANDOM.VALUE(1, 10)), TRUNC(SYSDATE) - ROUND(DBMS_RANDOM.VALUE(1, 10))); END LOOP; COMMIT; END;------ 测试数据(因为随机数关系,结果会不同) SQL> SELECT * FROM TEST_T;
BEGIN_DATE END_DATE ----------- ----------- 2009-6-3 2009-6-10 2009-6-7 2009-6-8 2009-6-3 2009-6-9 2009-6-4 2009-6-8 2009-6-9 2009-6-2 2009-6-7 2009-6-8 2009-6-7 2009-6-3 2009-6-2 2009-6-8 2009-6-6 2009-6-6 2009-6-4 2009-6-3 2009-6-1 2009-6-2 2009-6-3 2009-6-2 2009-6-4 2009-6-4 2009-6-8 2009-6-8 2009-6-2 2009-6-9 2009-6-7 2009-6-1 2009-6-9 2009-6-6 2009-6-1 2009-6-8 2009-6-9 2009-6-9 2009-6-6 2009-6-6 --------- 查询sql SELECT BEGIN_DATE, END_DATE FROM ( ---- SELECT R.*, R.END_DATE - R.BEGIN_DATE AS CZ, --- 差值 MIN(R.END_DATE - R.BEGIN_DATE) OVER(PARTITION BY NULL) MIN_CZ ---最小差值 FROM (SELECT T.*, COUNT(*) OVER(PARTITION BY T.BEGIN_DATE) AS CT_B, ---- BEGIN_DATE的计数 COUNT(DISTINCT T.END_DATE) OVER(PARTITION BY T.BEGIN_DATE) AS CT_E ----END_DATE的计数 FROM TEST_T T) R WHERE R.CT_B = R.CT_E --- 满足条件1 ) RR WHERE RR.CZ = MIN_CZ ---满足条件2--------- 结果 BEGIN_DATE END_DATE ----------- ----------- 2009-6-9 2009-6-2
SELECT t_startdate, min(t_enddate) FROM ( SELECT t_startdate, t_enddate FROM TDATE WHERE t_startdate IN ( SELECT t_startdate FROM TDATE GROUP BY t_startdate HAVING COUNT(t_startdate) > 1 ) GROUP BY t_startdate, t_enddate HAVING COUNT(*) = 1 ) B GROUP BY t_startdate
GROUP BY B.begin_date HAVING COUNT(*)>1) AND (A.end_date-A.begin_date) =
(SELE MIN(C.end_date-C.begin_date) FROM (
(SELECT D.begin_date FROM table D GROUP BY D.begin_date HAVING COUNT(*)>1) C
)这个SQL应该能实现你要的效果!
SQL> DESC TEST_T;
Name Type Nullable Default Comments
---------- ---- -------- ------- --------
BEGIN_DATE DATE Y
END_DATE DATE Y ------ 插入测试数据
BEGIN
DELETE FROM TEST_T;
COMMIT;
FOR V_I IN 1 .. 20
LOOP
INSERT INTO TEST_T
VALUES
(TRUNC(SYSDATE) - ROUND(DBMS_RANDOM.VALUE(1, 10)),
TRUNC(SYSDATE) - ROUND(DBMS_RANDOM.VALUE(1, 10)));
END LOOP;
COMMIT;
END;------ 测试数据(因为随机数关系,结果会不同)
SQL> SELECT * FROM TEST_T;
BEGIN_DATE END_DATE
----------- -----------
2009-6-3 2009-6-10
2009-6-7 2009-6-8
2009-6-3 2009-6-9
2009-6-4 2009-6-8
2009-6-9 2009-6-2
2009-6-7 2009-6-8
2009-6-7 2009-6-3
2009-6-2 2009-6-8
2009-6-6 2009-6-6
2009-6-4 2009-6-3
2009-6-1 2009-6-2
2009-6-3 2009-6-2
2009-6-4 2009-6-4
2009-6-8 2009-6-8
2009-6-2 2009-6-9
2009-6-7 2009-6-1
2009-6-9 2009-6-6
2009-6-1 2009-6-8
2009-6-9 2009-6-9
2009-6-6 2009-6-6
--------- 查询sql
SELECT BEGIN_DATE,
END_DATE
FROM ( ----
SELECT R.*,
R.END_DATE - R.BEGIN_DATE AS CZ, --- 差值
MIN(R.END_DATE - R.BEGIN_DATE) OVER(PARTITION BY NULL) MIN_CZ ---最小差值
FROM (SELECT T.*,
COUNT(*) OVER(PARTITION BY T.BEGIN_DATE) AS CT_B, ---- BEGIN_DATE的计数
COUNT(DISTINCT T.END_DATE) OVER(PARTITION BY T.BEGIN_DATE) AS CT_E ----END_DATE的计数
FROM TEST_T T) R
WHERE R.CT_B = R.CT_E --- 满足条件1
) RR
WHERE RR.CZ = MIN_CZ ---满足条件2--------- 结果
BEGIN_DATE END_DATE
----------- -----------
2009-6-9 2009-6-2
FROM
(
SELECT t_startdate, t_enddate
FROM TDATE
WHERE t_startdate IN
(
SELECT t_startdate
FROM TDATE
GROUP BY t_startdate
HAVING COUNT(t_startdate) > 1
)
GROUP BY t_startdate, t_enddate
HAVING COUNT(*) = 1
) B
GROUP BY t_startdate