如何判断同一人是否存在重复或交叉年月，数据如下

[jaydom]

name qsny zzny
张三 199605 199612
张三 199701 199705
张三 199702 199704
张三 199801 199805
张三 199806 199812
张四 199605 199612
张四 199701 199705
张四 199706 199712
张四 199801 199805
张四 199806 199812
张五 199605 199612
张五 199701 199705
张五 199706 199712
张五 199801 199802
张六 199605 199612
张六 199701 199712
张六 199801 199812
张六 199901 199912
张六 200001 200012
张六 200101 200112
张六 200201 200201
张六 200202 200212
张六 200301 200312
张六 200401 200412
张六 200501 200512
张六 200601 200612
张六 200701 200712
张六 200801 200806
张六 200807 200812
张七 199409 199506
张七 199507 199512
张七 199512 199512
张七 199601 199612
张七 199701 199705
张七 199706 199712
张七 199801 199805
张七 199806 199812
张七 199901 199906
张七 199907 199912
张七 200001 200012
张七 200101 200112
张七 200201 200212
张七 200301 200312
张七 200401 200412
张七 200501 200512
张七 200601 200612
张七 200701 200712
张七 200801 200806
张七 200807 200812
张八 199409 199506
张八 199507 199512
张八 199512 199512
张八 199601 199612
张八 199701 199705
张八 199706 199712
张八 199801 199805
张八 199806 199812
张八 199901 199906
张八 199907 199912
张八 200001 200012
张八 200101 200112
张八 200201 200212
张八 200301 200312
张八 200401 200412
张八 200501 200512
张八 200601 200612
张八 200701 200712
张八 200801 200806
张八 200807 200812
张八 200807 200812

[有问又问]

我就知道一点点思路，首先把得到的数据按升序排列；然后，判断第二条数据的每个数据是否 在第一条数据的中间，如果在他的中间则纯在交叉数据，否则不存在！

[jaydom]

我也有思路就是不会写。。。。

[xu176032]

select name,qsny,zzny from 表 group by name,qsny,zzny having count(1)>1

[jaydom]

3楼的sql 结果查不出来

[xu176032]

引用 4 楼 jaydom 的回复:

3楼的sql 结果查不出来

我的错，没仔细看

select * from 表 t1
where exists (
select 1 from 表 t2 where t2.name = t1.name and t2.qsny<t1.qsny and t2.zzny>t1.zzny)

[jaydom]

引用 4 楼 jaydom 的回复:

3楼的sql 结果查不出来

请您仔细看一下需求，看一下数据

[xu176032]

引用 6 楼 jaydom 的回复:

引用 4 楼 jaydom 的回复:

3楼的sql 结果查不出来

请您仔细看一下需求，看一下数据

select * from 表 t1
where exists (
select 1 from 表 t2 where t2.name = t1.name and t2.qsny>=t1.qsny and t2.qsny<=t1.zzny)

思路已经给你了，改一下就可以了吧，什么都要现成的吗。。

[jaydom]

引用 7 楼 xu176032 的回复:

引用 6 楼 jaydom 的回复:

[quote=引用 4 楼 jaydom 的回复:] 3楼的sql 结果查不出来

请您仔细看一下需求，看一下数据

select * from 表 t1
where exists (
select 1 from 表 t2 where t2.name = t1.name and t2.qsny>=t1.qsny and t2.qsny<=t1.zzny)

思路已经给你了，改一下就可以了吧，什么都要现成的吗。。[/quote] 关键是您的这个查不出来啊

[mayanzs]

select A.name,A.qsny,A.zzny,B.name name1,B.qsny qsny1,B.zzny zzny1 from Table1 A,Table1 B
  where B.name=A.name and ((B.qsny between A.qsny and A.zzny) or (B.zzny between A.qsny and A.zzny)));

[qq_19278133]

select * from test001 t1 where t1.name in (select name from test001 t2 where t1.name = t2.name and t1.qsny < t2.qsny and t1.zzny > t2.qsny);

[zbdzjx]

语句如下，能查出8条记录。

with table1(name, qsny, zzny) as
(
select '张三','199605','199612' from dual union all
select '张三','199701','199705' from dual union all
select '张三','199702','199704' from dual union all
select '张三','199801','199805' from dual union all
select '张三','199806','199812' from dual union all
select '张四','199605','199612' from dual union all
select '张四','199701','199705' from dual union all
select '张四','199706','199712' from dual union all
select '张四','199801','199805' from dual union all
select '张四','199806','199812' from dual union all
select '张五','199605','199612' from dual union all
select '张五','199701','199705' from dual union all
select '张五','199706','199712' from dual union all
select '张五','199801','199802' from dual union all
select '张六','199605','199612' from dual union all
select '张六','199701','199712' from dual union all
select '张六','199801','199812' from dual union all
select '张六','199901','199912' from dual union all
select '张六','200001','200012' from dual union all
select '张六','200101','200112' from dual union all
select '张六','200201','200201' from dual union all
select '张六','200202','200212' from dual union all
select '张六','200301','200312' from dual union all
select '张六','200401','200412' from dual union all
select '张六','200501','200512' from dual union all
select '张六','200601','200612' from dual union all
select '张六','200701','200712' from dual union all
select '张六','200801','200806' from dual union all
select '张六','200807','200812' from dual union all
select '张七','199409','199506' from dual union all
select '张七','199507','199512' from dual union all
select '张七','199512','199512' from dual union all
select '张七','199601','199612' from dual union all
select '张七','199701','199705' from dual union all
select '张七','199706','199712' from dual union all
select '张七','199801','199805' from dual union all
select '张七','199806','199812' from dual union all
select '张七','199901','199906' from dual union all
select '张七','199907','199912' from dual union all
select '张七','200001','200012' from dual union all
select '张七','200101','200112' from dual union all
select '张七','200201','200212' from dual union all
select '张七','200301','200312' from dual union all
select '张七','200401','200412' from dual union all
select '张七','200501','200512' from dual union all
select '张七','200601','200612' from dual union all
select '张七','200701','200712' from dual union all
select '张七','200801','200806' from dual union all
select '张七','200807','200812' from dual union all
select '张八','199409','199506' from dual union all
select '张八','199507','199512' from dual union all
select '张八','199512','199512' from dual union all
select '张八','199601','199612' from dual union all
select '张八','199701','199705' from dual union all
select '张八','199706','199712' from dual union all
select '张八','199801','199805' from dual union all
select '张八','199806','199812' from dual union all
select '张八','199901','199906' from dual union all
select '张八','199907','199912' from dual union all
select '张八','200001','200012' from dual union all
select '张八','200101','200112' from dual union all
select '张八','200201','200212' from dual union all
select '张八','200301','200312' from dual union all
select '张八','200401','200412' from dual union all
select '张八','200501','200512' from dual union all
select '张八','200601','200612' from dual union all
select '张八','200701','200712' from dual union all
select '张八','200801','200806' from dual union all
select '张八','200807','200812' from dual union all
select '张八','200807','200812' from dual
)
, table2 as
(select rownum rn, name, qsny, zzny from table1)
select * 
from table2 a 
where exists 
(
    select 1 
    from table2 b 
    where a.name=b.name and a.rn<>b.rn 
    and 
    (
        (a.qsny>=b.qsny and a.qsny<=b.zzny)
        or (a.zzny>=b.qsny and a.zzny<=b.zzny)
        or (a.qsny<=b.qsny and a.zzny>=b.zzny)
    )
)

[JuanRiquelme]

SELECT NAME, QSNY, ZZNY FROM (SELECT NAME, QSNY, ZZNY, LAG(ZZNY) OVER(PARTITION BY NAME ORDER BY NAME, QSNY) AS LAG_, LEAD(ZZNY) OVER(PARTITION BY NAME ORDER BY NAME, QSNY) AS LEAD_ FROM TAB) WHERE QSNY <= LAG_ OR ZZNY >= LEAD_ ORDER BY NAME DESC, QSNY;