declare @test table(CBMCU char(6),CBAN8 char(6),CBLITM char(15),CBCRCD char(3),CBUOM char(2), CBUORG char(1),StartDate datetime,EndDate datetime)
insert @test select '381001','110995','02250044-275','USD','EA','1','2008-10-20','2009-04-30'
union all
select '381001','110995','02250044-716','USD','EA','1','2008-10-20','2009-04-30'
union all
select '381001','110995','02250044-058','USD','EA','1','2008-10-20','2009-04-30'
union all
select '381001','110995','02250044-058','USD','EA','1','2009-03-20','2010-04-30'
union all
select '381001','110995','02250044-058','USD','EA','1','2010-03-20','2010-12-30'
union all
select '381001','110995','02250044-069','USD','EA','1','2008-10-20','2009-12-30'
union all
select '381001','110995','02250044-069','USD','EA','1','2009-02-26','2010-12-30'
union all
select '381002','110995','02250044-078','USD','EA','1','2008-10-20','2009-04-30'
union all
select '381100','110995','02250044-098','USD','EA','1','2008-10-20','2009-04-30'对同一个CBMCU,同一个CBAN8,同一个CBLITM,如果存在两条以上的记录,需要判断这些记录‘StartDate’与‘EndDate’有没有交集,如果有,则把所有这些记录取出来可能会存在的难点:对同一个CBMCU,同一个CBAN8,同一个CBLITM,如果存在三条以上的记录,在做判断的时候可能要考虑循环判断
解决方案 »
- 现在有何软件或办法在互联网里几台没有固定IP,远程访问SQL200?
- 一个查询语句排序问题,希望大家指导指导。
- Log Explorer如何查看网络服务器上的日志?
- 使用LOG Explorer 提示无法找到存储过程 master..xp_leserver_logattach
- truncate是骗人的?怎么可以恢复数据?
- 安装sql server 2005弹出错误 无法在com+目录中安装和配置程序集。。。。
- 如何从执行存储过程后得到的结果里执行select语句?
- SQL Server
- 那位老大帮我看看“cannot open user default database '<ID>'.using master database instead”
- 急需中文汉字和其拼音对应的词库,各种格式的均可,最好是txt的,给高分!
- 目录式表结构
- 大数据量操作经常无反应
declare @test table(CBMCU char(6),CBAN8 char(6),CBLITM char(15),CBCRCD char(3),CBUOM char(2), CBUORG char(1),StartDate datetime,EndDate datetime)
insert @test select '381001','110995','02250044-275','USD','EA','1','2008-10-20','2009-04-30'
union all
select '381001','110995','02250044-716','USD','EA','1','2008-10-20','2009-04-30'
union all
select '381001','110995','02250044-058','USD','EA','1','2008-10-20','2009-04-30'
union all
select '381001','110995','02250044-058','USD','EA','1','2009-03-20','2010-04-30'
union all
select '381001','110995','02250044-058','USD','EA','1','2010-03-20','2010-12-30'
union all
select '381001','110995','02250044-069','USD','EA','1','2008-10-20','2009-12-30'
union all
select '381001','110995','02250044-069','USD','EA','1','2009-02-26','2010-12-30'
union all
select '381002','110995','02250044-078','USD','EA','1','2008-10-20','2009-04-30'
union all
select '381100','110995','02250044-098','USD','EA','1','2008-10-20','2009-04-30'SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY CBMCU) 'ID',* FROM @TEST) T1
WHERE EXISTS(
SELECT 1 FROM (SELECT ROW_NUMBER() OVER(ORDER BY CBMCU) 'ID',* FROM @TEST) T2 WHERE T2.CBMCU=T1.CBMCU AND T2.CBLITM=T1.CBLITM AND T2.CBAN8=T1.CBAN8
AND (T2.StartDate BETWEEN T1.StartDate AND T1.EndDate OR T2.ENDDate BETWEEN T1.StartDate AND T1.EndDate
OR T1.StartDate BETWEEN T2.StartDate AND T2.EndDate OR T1.ENDDate BETWEEN T2.StartDate AND T2.EndDate)
AND T2.ID<>T1.ID
)
/*
ID CBMCU CBAN8 CBLITM CBCRCD CBUOM CBUORG StartDate EndDate
-------------------- ------ ------ --------------- ------ ----- ------ ----------------------- -----------------------
3 381001 110995 02250044-058 USD EA 1 2008-10-20 00:00:00.000 2009-04-30 00:00:00.000
4 381001 110995 02250044-058 USD EA 1 2009-03-20 00:00:00.000 2010-04-30 00:00:00.000
5 381001 110995 02250044-058 USD EA 1 2010-03-20 00:00:00.000 2010-12-30 00:00:00.000
6 381001 110995 02250044-069 USD EA 1 2008-10-20 00:00:00.000 2009-12-30 00:00:00.000
7 381001 110995 02250044-069 USD EA 1 2009-02-26 00:00:00.000 2010-12-30 00:00:00.000
*/
where exists(select 1 from @test
where CBMCU=t.CBMCU and CBAN8=t.CBAN8 and CBLITM=t.CBLITM and id<>t.id
and ((StartDate between t.StartDate and t.EndDate) or (EndDate between t.StartDate and t.EndDate) or (StartDate < t.StartDate and EndDate>t.EndDate))
)
非常感谢