序号 名字 纳税起日期 纳税止日期
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.namefrom (select tab1.*,connect_by_root(faxstart) rootstart from tab1 where connect_by_isleaf = 1 connect by faxstart = prior faxend + 1) Awhere 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','yyyy-mm-dd') end_date FROM DUAL UNION ALL 4 SELECT 3 seq,'AA' tname,TO_DATE('2010-06-11','yyyy-mm-dd') start_date,TO_DATE('2010-08-31','yyyy-mm-dd') end_date FROM DUAL UNION ALL 5 SELECT 4 seq,'AA' tname,TO_DATE('2011-01-12','yyyy-mm-dd') start_date,TO_DATE('2011-06-07','yyyy-mm-dd') end_date FROM DUAL UNION ALL 6 SELECT 5 seq,'BB' tname,TO_DATE('2009-01-01','yyyy-mm-dd') start_date,TO_DATE('2009-05-08','yyyy-mm-dd') end_date FROM DUAL UNION ALL 7 SELECT 6 seq,'BB' tname,TO_DATE('2010-01-01','yyyy-mm-dd') start_date,TO_DATE('2010-12-08','yyyy-mm-dd') end_date FROM DUAL UNION ALL 8 SELECT 7 seq,'BB' tname,TO_DATE('2011-02-01','yyyy-mm-dd') start_date,TO_DATE('2012-01-08','yyyy-mm-dd') end_date FROM DUAL UNION ALL 9 SELECT 8 seq,'BB' tname,TO_DATE('2014-01-01','yyyy-mm-dd') start_date,TO_DATE('2014-11-08','yyyy-mm-dd') end_date FROM DUAL 10 ) 11 SELECT m.tname, 12 MIN(m.start_date) start_date, 13 MAX(m.end_date) end_date 14 FROM (SELECT t.*, 15 LAG(t.end_date, 1) OVER(PARTITION BY t.tname ORDER BY t.start_date) last_end_date 16 FROM t) m 17 GROUP BY m.tname, 18 NVL(MONTHS_BETWEEN(TO_DATE(TO_CHAR(m.start_date, 'yyyymm') || '01'), DECODE(last_end_date, NULL, NULL, TO_DATE(TO_CHAR(last_end_date, 'yyyymm') || '01'))), 1) 19 HAVING MONTHS_BETWEEN(TO_DATE(TO_CHAR(MAX(m.end_date),'yyyymm') || '01'),TO_DATE(TO_CHAR(MIN(m.start_date),'yyyymm') || '01')) >= 11 20 ORDER BY 1,2 21 ;TNAME START_DATE END_DATE----- ----------- -----------AA 2009/01/01 2010/08/31BB 2010/01/01 2010/12/08BB 2011/02/01 2012/01/08