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

2015-09-10 04:21:09 426 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

js 判断多组时间段是否交叉重复今天在工作中遇到一个需求，页面上可以填写多个开始，结束时间段，但提交时不能有重复交叉的情况，一开始网上搜了一下，也没找到方便的方法，就自己想了想写了个，代码如下： function compareDate(){ var begin = ['2015-01-02 10:01:38','2015-01-04 0 阅读详情

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

...全文

华为AR6140E/S5735设备密码策略配置全攻略：从复杂度到锁定机制本文详细介绍了华为AR6140E/S5735设备的密码策略配置方法，涵盖密码复杂度、时效性和防护性三大核心维度。通过具体的CLI命令示例，指导管理员如何设置密码长度、字符组合、有效期及登录失败锁定等安全机制，为企业网络提供全面的用户名密码防护策略。阅读详情

jaydom

Oracle 17,133 成员 55,258 内容

+ 关注 + 加入社区

切换为时间正序

JuanRiquelme 2015-09-22

打赏
举报

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;

zbdzjx 2015-09-14

打赏
举报

语句如下，能查出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)
    )
)

qq_19278133 2015-09-12

打赏
举报

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);

mayanzs 2015-09-11

打赏
举报


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)));

jaydom 2015-09-10

打赏
举报

引用 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] 关键是您的这个查不出来啊

xu176032 2015-09-10

打赏
举报

引用 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 2015-09-10

打赏
举报

引用 4 楼 jaydom 的回复:

3楼的sql 结果查不出来

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

xu176032 2015-09-10

打赏
举报

引用 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 2015-09-10

打赏
举报

3楼的sql 结果查不出来

xu176032 2015-09-10

打赏
举报


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

jaydom 2015-09-10

打赏
举报

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

有问又问 2015-09-10

打赏
举报

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

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

相关推荐