declare @t table(ID int,DATE1 datetime,DATE2 datetime)
insert into @t select 1,'2008/01/01','2008/01/08'
insert into @t select 1,'2008/01/05','2008/01/09'
insert into @t select 1,'2008/01/11','2008/01/20'
insert into @t select 1,'2008/01/22','2008/01/27'
insert into @t select 2,'2008/01/04','2008/01/06'
insert into @t select 2,'2008/01/08','2008/01/13'
select
a.*
from
@t a,@t b
where
a.ID=b.ID
and
(a.DATE1!=b.DATE1 or a.DATE2!=b.DATE2)
and
((a.DATE1 between b.DATE1 and b.DATE2) or (a.DATE2 between b.DATE1 and b.DATE2))/*
ID DATE1 DATE2
----------- ------------------------------------------------------ ------------------------------------------------------
1 2008-01-01 00:00:00.000 2008-01-08 00:00:00.000
1 2008-01-05 00:00:00.000 2008-01-09 00:00:00.000
*/
解决方案 »
- MS SQL2005简单问题
- 一个简单的数据库选项的设置问题
- 求一个update触发器
- 如何得到动态SQL中的赋给变量的值?
- 关于插入数据的问题
- dts导入的问题。
- 请问select查询语句如何调用存储过程?
- Windows 2003 Server 怎么装不了SQL Server 2000?
- ******如何往数据库里填入,除了有默认值的那一列以外的,其他列的值??
- MS SQL SERVER都有那些版本?什么区别啊?
- 我要通过函数计算一种统计结果放在输出字段中,但该函数中需要执行动态SQL,而函数中不允许执行动态SQL,提示错误:在函数内的'insert exec' 对带副作用的运算符使用无效
- 数据导出问题
-- Author: happyflystone
-- Version:V1.001
-- Date:2009-01-14 16:59:12
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ID int,DATE1 smalldatetime,DATE2 smalldatetime)
Go
Insert into ta
select 1,'2008/1/1','2008/1/8' union all
select 1,'2008/1/5','2008/1/9' union all
select 1,'2008/1/11','2008/1/20' union all
select 1,'2008/1/22','2008/1/27' union all
select 2,'2008/1/4','2008/1/6' union all
select 2,'2008/1/8','2008/1/13'
Go
--Start
Select * from ta a
where exists(select 1 from ta where id = a.id and a.date1 <> date1 and a.date2 <> date2 and (a.date1 between date1 and date2 or a.date2 between date1 and date2))
--Result:
/*
ID DATE1 DATE2
----------- ------------------------------------------------------ ------------------------------------------------------
1 2008-01-01 00:00:00 2008-01-08 00:00:00
1 2008-01-05 00:00:00 2008-01-09 00:00:00(所影响的行数为 2 行)
*/
--End
GO
CREATE TABLE TB(ID INT, DATE1 DATETIME, DATE2 DATETIME)
--------------------
INSERT TB SELECT 1 , '2008/1/1', '2008/1/8'
INSERT TB SELECT 1 , '2008/1/5', '2008/1/9'
INSERT TB SELECT 1 , '2008/1/11', '2008/1/20'
INSERT TB SELECT 1 , '2008/1/22' , '2008/1/27'
INSERT TB SELECT 2 , '2008/1/4' , '2008/1/6'
INSERT TB SELECT 2, '2008/1/8' ,'2008/1/13'
SELECT A.* FROM TB A INNER JOIN TB B ON A.ID=B.ID AND(A.DATE1<>B.DATE1 or A.DATE2<>B.DATE2)
and((A.DATE1 between B.DATE1 and B.DATE2) or (A.DATE2 between B.DATE1 and B.DATE2))
/*ID DATE1 DATE2
----------- ------------------------------------------------------ ------------------------------------------------------
1 2008-01-01 00:00:00.000 2008-01-08 00:00:00.000
1 2008-01-05 00:00:00.000 2008-01-09 00:00:00.000*/
declare @table table(id int,date1 datetime,date2 datetime)
insert into @table
select 1,'2008-1-1','2008-1-8' union all
select 1,'2008-1-5','2008-1-9' union all
select 1,'2008-1-11','2008-1-20' union all
select 1,'2008-1-22','2008-1-27' union all
select 2,'2008-1-4','2008-1-6' union all
select 2,'2008-1-8','2008-1-13' union all
select 3,'2008-1-4','2008-1-6' union all
select 3,'2008-1-5','2008-1-13' select a.*,id2=identity(int,1,1) into #temp from @table aselect a.id,a.date2 from #temp a inner join #temp b on a.id2+1=b.id2 and a.id=b.id
where a.date2>b.date1
--> 测试数据:[T]
if object_id('[T]') is not null drop table [T]
create table [T]([ID] int,[DATE1] datetime,[DATE2] datetime)
insert [T]
select 1,'2008/1/1','2008/1/8' union all
select 1,'2008/1/5','2008/1/9' union all
select 1,'2008/1/11','2008/1/20' union all
select 1,'2008/1/22','2008/1/27' union all
select 2,'2008/1/4','2008/1/6' union all
select 2,'2008/1/8','2008/1/13'select * from T a
where (select count(*) from T b where ID=a.ID and DATE1<a.DATE2 and DATE2>a.DATE1)>1
/*
ID DATE1 DATE2
----------- ----------------------- -----------------------
1 2008-01-01 00:00:00.000 2008-01-08 00:00:00.000
1 2008-01-05 00:00:00.000 2008-01-09 00:00:00.000(2 行受影响)
*/drop table T
1 2008/1/5 2008/1/8
1 2008/1/1 2008/1/9 第二条记录完全包含了第一条,用between来判断的话,第一与第二条交差了,但第二条没有与第一条交差。
select * from T a
where (select count(*) from T b where ID=a.ID and DATE1<=a.DATE2 and DATE2>=a.DATE1)>1
/*
ID DATE1 DATE2
----------- ----------------------- -----------------------
1 2008-01-01 00:00:00.000 2008-01-08 00:00:00.000
1 2008-01-05 00:00:00.000 2008-01-09 00:00:00.000(2 行受影响)
*/
id int ,
startDate datetime,
endDate datetime)
insert into OverlapDate
select 1,'2008-1-1','2008-1-8'
union all
select 1,'2008-1-5','2008-1-9'
union all
select 1,'2008-1-11','2008-1-20'
union all
select 2,'2008-1-1','2008-1-3'
union all
select 2,'2008-1-4','2008-1-8'
union all
select 3,'2008-1-1','2008-1-8'
union all
select 3,'2008-1-9','2008-1-10'
declare @test table(seriesNumber int identity(1,1),
id int,
startDate datetime,
endDate datetime)
insert into @test(id,startDate,endDate)
select * from OverlapDate
order by id,startDate
select * from @test t0
where t0.endDate > (select startDate from @test t1
where t0.id = t1.id and
t1.seriesNumber = t0.seriesNumber+1)
/*
seriesNumber id startDate endDate
-------------- --- --------------- ------------
1 1 2008-01-01 00:00:00.000 2008-01-08 00:00:00.000
4 2 2008-01-01 00:00:00.000 2008-01-05 00:00:00.000
(所影响的行数为 2 行)
*/
/*
seriesNumber id startDate endDate
-------------- --- --------------- ------------
1 1 2008-01-01 00:00:00.000 2008-01-08 00:00:00.000
4 2 2008-01-01 00:00:00.000 2008-01-05 00:00:00.000
(所影响的行数为 2 行)
*/
insert into tb values(1 , '2008/1/1' , '2008/1/8')
insert into tb values(1 , '2008/1/5' , '2008/1/9')
insert into tb values(1 , '2008/1/11', '2008/1/20')
insert into tb values(1 , '2008/1/22', '2008/1/27')
insert into tb values(2 , '2008/1/4' , '2008/1/6')
insert into tb values(2 , '2008/1/8' , '2008/1/13')
go
--使用一个临时表
select top 8000 identity(int,0,1) as id into # from syscolumns a,syscolumns bselect id , DATE from
(
select n.id , dateadd(day , m.id , n.DATE1) DATE from # m , tb n where dateadd(day , m.id , n.DATE1) <= n.DATE2
) t
group by id , date
having count(*) > 1drop table tb,#/*
id DATE
----------- ------------------------------------------------------
1 2008-01-05 00:00:00.000
1 2008-01-06 00:00:00.000
1 2008-01-07 00:00:00.000
1 2008-01-08 00:00:00.000(所影响的行数为 4 行)
*/