急！！！~多条记录 连续日期的 合并问题
序号 名字 纳税起日期 纳税止日期
1 张三 2009-01-01 2009-07-31
2 张三 2009-08-01 2010-05-31
3 张三 2010-06-01 2010-08-31
4 张三 2011-01-12 2011-06-07
5 李四 2009-01-01 2009-05-08
 


要查询出连续纳税一年以上的人名字和起止纳税日期

------解决方案--------------------
WITH tab1 AS (SELECT 1 id,
'张三' name,
TO_DATE ('2009-01-01', 'yyyy-mm-dd') faxstart,
TO_DATE ('2009-07-31', 'yyyy-mm-dd') faxend
FROM DUAL
UNION ALL
SELECT 2 id,
'张三' name,
TO_DATE ('2009-08-01', 'yyyy-mm-dd') faxstart,
TO_DATE ('2010-05-31', 'yyyy-mm-dd') faxend
FROM DUAL
UNION ALL
SELECT 3 id,
'张三',
TO_DATE ('2010-06-01', 'yyyy-mm-dd') faxstart,
TO_DATE ('2010-08-31', 'yyyy-mm-dd') faxend
FROM DUAL
UNION ALL
SELECT 4 id,
'张三' name,
TO_DATE ('2011-01-12', 'yyyy-mm-dd') faxstart,
TO_DATE ('2011-06-07', 'yyyy-mm-dd') faxend
FROM DUAL
UNION ALL
SELECT 5 id,
'李四' name,
TO_DATE ('2009-01-01', 'yyyy-mm-dd') faxstart,
TO_DATE ('2009-05-08', 'yyyy-mm-dd') faxend
FROM DUAL)
SELECT name, faxstart, faxend
FROM ( SELECT name,
CONNECT_BY_ROOT (faxstart) faxstart,
faxend,
ROW_NUMBER ()
OVER (PARTITION BY name, faxend ORDER BY faxstart)
rn
FROM tab1
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR faxend + 1 = faxstart AND PRIOR name = name)
 WHERE rn = 1 AND ADD_MONTHS (faxstart, 12) <= faxend
------解决方案--------------------
select taxpayer, start_date, end_date
from tax_rec
 where taxpayer in
(select taxpayer
from tax_rec
where add_months(start_date, 12) < end_date)
------解决方案--------------------
借用下2楼的测试用数据，多谢。

SQL code

WITH tab1 AS (SELECT 1 id,
  '张三' name,
  TO_DATE ('2009-01-01', 'yyyy-mm-dd') faxstart,
  TO_DATE ('2009-07-31', 'yyyy-mm-dd') faxend
  FROM DUAL
  UNION ALL
  SELECT 2 id,
  '张三' name,
  TO_DATE ('2009-08-01', 'yyyy-mm-dd') faxstart,
  TO_DATE ('2010-05-31', 'yyyy-mm-dd') faxend
  FROM DUAL
  UNION ALL
  SELECT 3 id,
  '张三',
  TO_DATE ('2010-06-01', 'yyyy-mm-dd') faxstart,
  TO_DATE ('2010-08-31', 'yyyy-mm-dd') faxend
  FROM DUAL
  UNION ALL
  SELECT 4 id,
  '张三' name,
  TO_DATE ('2011-01-12', 'yyyy-mm-dd') faxstart,
  TO_DATE ('2011-06-07', 'yyyy-mm-dd') faxend
  FROM DUAL
  UNION ALL
  SELECT 5 id,
  '李四' name,
  TO_DATE ('2009-01-01', 'yyyy-mm-dd') faxstart,
  TO_DATE ('2009-05-08', 'yyyy-mm-dd') faxend
  FROM DUAL)
-- 上面借用了
select distinct A.name
from 
  (select tab1.*,connect_by_root(faxstart) rootstart
  from tab1 
  where connect_by_isleaf = 1 
  connect by faxstart = prior faxend + 1) A
where faxend >= add_months(rootstart,12)
;

------解决方案--------------------
SQL code

SQL> WITH t AS (
  2      SELECT 1 seq,'AA' tname,TO_DATE('2009-01-01','yyyy-mm-dd') start_date,TO_DATE('2009-07-31','yyyy-mm-dd') end_date FROM DUAL UNION ALL
  3      SELECT 2 seq,'AA' tname,TO_DATE('2009-08-02','yyyy-mm-dd') start_date,TO_DATE('2010-05-31','