求个SQL

Question

本帖最后由 luoyunying 于 2011-06-21 13:45:33 编辑

Answer 1

with aa as(
select 1 AS id,date '2011-01-01' AS qsrq,date '2011-02-01' as zzrq from dual union
select 1,date '2011-01-21',date '2011-03-01' from dual union
select 1,date '2011-05-01',date '2011-12-21' from dual union
select 2,date '2011-01-01',date '2011-12-31' from dual )
SELECT DISTINCT A.ID
  ,DECODE(A.F1,1,A.QSRQ,LAG(A.QSRQ)OVER(PARTITION BY A.ID ORDER BY A.QSRQ)) AS QSRQ
  ,DECODE(A.F2,1,A.ZZRQ,LEAD(A.ZZRQ)OVER(PARTITION BY A.ID ORDER BY A.QSRQ)) AS ZZRQ
FROM (
SELECT AA.*
,CASE WHEN AA.QSRQ -LAG(AA.ZZRQ)OVER(PARTITION BY AA.ID ORDER BY AA.QSRQ) <= 1 THEN 0 ELSE 1 END AS F1
,CASE WHEN LEAD(AA.QSRQ)OVER(PARTITION BY AA.ID ORDER BY AA.QSRQ) - AA.ZZRQ <= 1 THEN 0 ELSE 1 END AS F2
FROM AA) A
WHERE F1+F2 > 0
ORDER BY 1,2

Answer 2

with aa as(
select 1 AS id, 20110101 AS qsrq, 20110601 as zzrq from dual union
select 1, 20110121, 20110301 from dual union
select 1, 20110501, 20111221 from dual union
select 2, 20110101, 20111231 from dual union
select 2, 20111231, 20121231 from dual )
SELECT DISTINCT A.ID
  ,DECODE(A.F1,1,A.QSRQ,LAG(A.QSRQ)OVER(PARTITION BY A.ID ORDER BY A.QSRQ)) AS QSRQ
  ,DECODE(A.F2,1,A.ZZRQ,LEAD(A.ZZRQ)OVER(PARTITION BY A.ID ORDER BY A.QSRQ)) AS ZZRQ
FROM (
SELECT AA.*
,CASE WHEN AA.QSRQ -LAG(AA.ZZRQ)OVER(PARTITION BY AA.ID ORDER BY AA.QSRQ) <= 1 THEN 0 ELSE 1 END AS F1
,CASE WHEN LEAD(AA.QSRQ)OVER(PARTITION BY AA.ID ORDER BY AA.QSRQ) - AA.ZZRQ <= 1 THEN 0 ELSE 1 END AS F2
FROM AA) A
WHERE F1+F2 > 0
ORDER BY 1,2
第一个结果有点问题 应该 = 20110101，20110601 

Answer 3

with aa as(
select 1 AS id, 20110101 AS qsrq, 20110601 as zzrq from dual union
select 1, 20110121, 20110301 from dual union
select 1, 20110501, 20111221 from dual union
select 2, 20110101, 20111231 from dual union
select 2, 20111231, 20121231 from dual  )
SELECT DISTINCT A.ID
  ,DECODE(A.F1,1,A.QSRQ,LAG(A.QSRQ)OVER(PARTITION BY A.ID ORDER BY A.QSRQ)) AS QSRQ
  ,DECODE(A.F2,1,A.ZZRQ,LEAD(A.ZZRQ)OVER(PARTITION BY A.ID ORDER BY A.QSRQ)) AS ZZRQ
FROM (
SELECT AA.*
,CASE WHEN AA.QSRQ -LAG(AA.ZZRQ)OVER(PARTITION BY AA.ID ORDER BY AA.QSRQ) <= 1 THEN 0 ELSE 1 END AS F1
,CASE WHEN LEAD(AA.QSRQ)OVER(PARTITION BY AA.ID ORDER BY AA.QSRQ) - AA.ZZRQ <= 1 THEN 0 ELSE 1 END AS F2
FROM AA
WHERE NOT EXISTS(SELECT 1 FROM AA B WHERE B.ID = AA.ID 
  AND AA.QSRQ BETWEEN B.QSRQ AND B.ZZRQ
  AND AA.ZZRQ BETWEEN B.QSRQ AND B.ZZRQ
  AND (AA.QSRQ != B.QSRQ OR AA.ZZRQ != B.ZZRQ))
) A
WHERE F1+F2 > 0
ORDER BY 1,2

Answer 4

with aa as(
select 1 AS id, 20110101 AS qsrq, 20110601 as zzrq from dual union
select 1, 20110121, 20110301 from dual union
select 1, 20110501, 20111221 from dual union
select 2, 20110101, 20111231 from dual union
select 2, 20111231, 20121231 from dual  )
SELECT QS.ID,QS.QSRQ,ZZ.ZZRQ
FROM(
SELECT ID,QSRQ,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY QSRQ) AS RN
FROM AA
WHERE NOT EXISTS(SELECT 1 FROM AA B WHERE B.ID = AA.ID 
     AND AA.QSRQ > B.QSRQ AND AA.QSRQ < B.ZZRQ)
) QS
LEFT JOIN (SELECT ID,ZZRQ,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ZZRQ) AS RN
FROM AA
WHERE NOT EXISTS(SELECT 1 FROM AA B WHERE B.ID = AA.ID 
     AND AA.ZZRQ > B.QSRQ AND AA.ZZRQ < B.ZZRQ)
) ZZ ON QS.ID = ZZ.ID AND QS.RN = ZZ.RN

Answer 5

SQL> desc a;
Name   Type         Nullable Default Comments 
------ ------------ -------- ------- -------- 
ID     CHAR(1)      Y                         
S_DATE VARCHAR2(10) Y                         
E_DATE VARCHAR2(10) Y                         
 
SQL> select * from a;
 
ID S_DATE     E_DATE
-- ---------- ----------
s  20110101   20111231
s  20111231   20121231
t  20110101   20110601
t  20110121   20110301
t  20110501   20111221
 
SQL> 
SQL> SELECT id, MIN(s_date) s_date, MAX(e_date) e_date
  2    FROM a
  3   START WITH NOT EXISTS (SELECT 1
  4                 FROM a b
  5                WHERE b.e_date > a.s_date
  6                  AND b.e_date < a.e_date
  7                  AND a.id = b.id)
  8  CONNECT BY PRIOR a.e_date > a.s_date
  9         AND PRIOR a.e_date < a.e_date
 10         AND PRIOR a.id = a.id
 11   GROUP BY id, rownum - LEVEL;
 
ID S_DATE     E_DATE
-- ---------- ----------
s  20110101   20111231
s  20111231   20121231
t  20110101   20111221
 
SQL> 

Answer 6

     WHERE b.e_date >= a.s_date
  6                  AND b.e_date < a.e_date
  7                  AND a.id = b.id)
  8  CONNECT BY PRIOR a.e_date >= a.s_date
就完美了