sqlserver問題,超多分...

[colinliu]

數據庫表裡有如下數據:
A_no mno s_time e_time
200701001 000001 2007/01/01 14:20 2007/01/01 18:00
200701002 000001 2007/01/01 16:20 2007/01/01 20:00


其它數據省略.

請問我抓取mno相同，但是時間有交替或重復資料呢？

[一者仁心]

select * from tablename t where is exists (select count(0) from tablename where s_time<t.s_time and and mno=t.mno)

[colinliu]

B131 彭秀瓊 20080116 20080117
B131 彭秀瓊 20080107 20080108
B131 彭秀瓊 20080109 20080119
B465 龍芙蓉 20080109 20080110
b776 陳愛儀 20080301 20080301
B990 張榮英 20080109 20080110
C467 張白芳 20080109 20080110
C634 趙本會 20080109 20080110
C634 趙本會 20080107 20080108
C634 趙本會 20080112 20080131

樓上理解錯了,我數據庫中記錄如上邊所示.現在就是抓如交叉時間有重復的.
比如說
C634 20080103 20080121
C634 20080109 20080130
這兩個就等於是重復.

[pt1314917]

这样？
create table tr(A_no varchar(10),mno varchar(10),s_time datetime,e_time datetime)   
insert into tr select '200701001','000001','2007/01/01 14:20','2007/01/02 18:00'
insert into tr select '200701002','000001','2007/01/02 16:20','2007/01/05 20:00'   
insert into tr select '200701003','000002','2007/01/01 12:20','2007/01/01 15:00'   
insert into tr select '200701004','000002','2007/01/02 16:20','2007/01/03 20:00'   


select a.* from tr a, tr b where a.mno=b.mno and a.A_no<>b.A_no and datediff(dd,a.s_time,b.s_time)>0
and datediff(dd,a.e_time,b.s_time)>0
union all
select b.* from tr a, tr b where a.mno=b.mno and a.A_no<>b.A_no and datediff(dd,a.s_time,b.s_time)>0
and datediff(dd,a.e_time,b.s_time)>0

[昵称被占用了]

select  distinct mno  from tablename a 
where not exists (
 select 1 from tablename 
 where mno=a.mno
 and s_time<=a.e_time and eTime>=a.sTime
)

[dobear_0922]

create table tb(A_no varchar(10),mno varchar(10),s_time datetime,e_time datetime)   
insert into tb select '200701001','000001','2007/01/01 14:20','2007/01/02 18:00'
insert into tb select '200701002','000001','2007/01/02 16:20','2007/01/05 20:00'   
insert into tb select '200701003','000002','2007/01/01 12:20','2007/01/01 15:00'   
insert into tb select '200701004','000002','2007/01/02 16:20','2007/01/03 20:00'   


select * from tb 
where exists
(
	select 1 from tb t 
	where mno=tb.mno and A_no<>tb.A_no 
		and (e_time between tb.s_time and tb.e_time or tb.e_time between s_time and e_time)
)

/*
A_no       mno        s_time                  e_time
---------- ---------- ----------------------- -----------------------
200701001  000001     2007-01-01 14:20:00.000 2007-01-02 18:00:00.000
200701002  000001     2007-01-02 16:20:00.000 2007-01-05 20:00:00.000

(2 row(s) affected)
*/

drop table tb

[dobear_0922]

create table tb(A_no varchar(10),mno varchar(10),s_time datetime,e_time datetime)   
insert tb select 'B131', '彭秀瓊', '20080116', '20080117' 
union all select 'B131', '彭秀瓊', '20080107', '20080108' 
union all select 'B131', '彭秀瓊', '20080109', '20080119' 
union all select 'B465', '龍芙蓉', '20080109', '20080110' 
union all select 'b776', '陳愛儀', '20080301', '20080301' 
union all select 'B990', '張榮英', '20080109', '20080110' 
union all select 'C467', '張白芳', '20080109', '20080110' 
union all select 'C634', '趙本會', '20080109', '20080110' 
union all select 'C634', '趙本會', '20080107', '20080108' 
union all select 'C634', '趙本會', '20080112', '20080131'   


select A_no, s_time=convert(varchar(10), s_time, 120), e_time=convert(varchar(10), e_time, 120)
from tb 
where exists
(
	select 1 from tb t 
	where mno=tb.mno and s_time<>tb.s_time
		and (e_time between tb.s_time and tb.e_time or tb.e_time between s_time and e_time)
)

/*
A_no       s_time     e_time
---------- ---------- ----------
B131       2008-01-16 2008-01-17
B131       2008-01-09 2008-01-19

(2 row(s) affected)
*/

drop table tb

[dawugui]

[code=SQL]create table tb(A_no varchar(10),mno varchar(10),s_time datetime,e_time datetime)
insert into tb values('B131', '彭秀瓊', '20080116', '20080117') 
insert into tb values('B131', '彭秀瓊', '20080107', '20080108') 
insert into tb values('B131', '彭秀瓊', '20080109', '20080119') 
insert into tb values('B465', '龍芙蓉', '20080109', '20080110') 
insert into tb values('b776', '陳愛儀', '20080301', '20080301') 
insert into tb values('B990', '張榮英', '20080109', '20080110') 
insert into tb values('C467', '張白芳', '20080109', '20080110') 
insert into tb values('C634', '趙本會', '20080109', '20080110') 
insert into tb values('C634', '趙本會', '20080107', '20080108') 
insert into tb values('C634', '趙本會', '20080112', '20080131') 
go

select id=identity(int,1,1) , * into tb1 from tb

SELECT TOP 8000 id = identity(int,0,1) INTO tb2 FROM syscolumns a, syscolumns b  

select distinct m.* from tb m,
(
  select mno , mytime from
  (
    select tb1.id , tb1.mno , dateadd(day , tb2.id , tb1.s_time) mytime from tb1 , tb2 where dateadd(day , tb2.id , tb1.s_time) <= tb1.e_time
  ) t
  group by mno , mytime having count(*) > 1
) n
where m.mno = n.mno and m.s_time <= n.mytime and m.e_time >= n.mytime

drop table tb,tb1,tb2

/*
A_no       mno        s_time                                                 e_time                                                 
---------- ---------- ------------------------------------------------------ ------------------------------------------------------ 
B131       彭秀瓊        2008-01-09 00:00:00.000                                2008-01-19 00:00:00.000
B131       彭秀瓊        2008-01-16 00:00:00.000                                2008-01-17 00:00:00.000

（所影响的行数为 2 行）
*/

[/code]

[TNT_1st_excellence]

楼上正解!

[yang_dgut]

create table tb(A_no varchar(10),mno varchar(10),s_time datetime,e_time datetime)
insert into tb values('B131', '彭秀瓊', '20080116', '20080117') 
insert into tb values('B131', '彭秀瓊', '20080107', '20080108') 
insert into tb values('B131', '彭秀瓊', '20080109', '20080119') 
insert into tb values('B465', '龍芙蓉', '20080109', '20080110') 
insert into tb values('b776', '陳愛儀', '20080301', '20080301') 
insert into tb values('B990', '張榮英', '20080109', '20080110') 
insert into tb values('C467', '張白芳', '20080109', '20080110') 
insert into tb values('C634', '趙本會', '20080109', '20080110') 
insert into tb values('C634', '趙本會', '20080107', '20080108') 
insert into tb values('C634', '趙本會', '20080112', '20080131') 
go

select id=identity(int,1,1) , * into tb1 from tb

SELECT TOP 8000 id = identity(int,0,1) INTO tb2 FROM syscolumns a, syscolumns b  

select distinct m.* from tb m,
(
  select mno , mytime from
  (
    select tb1.id , tb1.mno , dateadd(day , tb2.id , tb1.s_time) mytime from tb1 , tb2 where dateadd(day , tb2.id , tb1.s_time) <= tb1.e_time
  ) t
  group by mno , mytime having count(*) > 1
) n
where m.mno = n.mno and m.s_time <= n.mytime and m.e_time >= n.mytime

drop table tb,tb1,tb2

[wyb0026]

很有意思明天再看看吧，俺要下班了。留个脚印先

[JiangHongTao]

select a_no,count(*) from tb a
where exists(select * from tb where a_no=a.a_no 
     and (e_time between a.e_time and a.s_time 
       or s_time between a.e_time and a.s_time))
group by a_no

这个最简单了，结贴吧LZ。

[wyb0026]

create table tb(A_no varchar(10),mno varchar(10),s_time datetime,e_time datetime)   
insert tb select 'B131', '彭秀瓊', '20080116', '20080117' 
union all select 'B131', '彭秀瓊', '20080107', '20080108' 
union all select 'B131', '彭秀瓊', '20080109', '20080119' 
union all select 'B465', '龍芙蓉', '20080109', '20080110' 
union all select 'b776', '陳愛儀', '20080301', '20080301' 
union all select 'B990', '張榮英', '20080109', '20080110' 
union all select 'C467', '張白芳', '20080109', '20080110' 
union all select 'C634', '趙本會', '20080109', '20080110' 
union all select 'C634', '趙本會', '20080107', '20080108' 
union all select 'C634', '趙本會', '20080112', '20080131'   


select a.* from tb a inner join tb b on a.A_no=b.A_no
where (a.s_time>b.s_time and a.s_time<b.e_time) or (a.e_time>b.s_time and a.e_time<b.e_time)
      or (b.s_time>a.s_time and b.s_time<a.e_time) or (b.e_time>a.s_time and b.e_time<a.e_time)	

drop table tb

[tim_spac]

select * from table a
where exists (select 1 from table b
where b.mno=a.mo -- mo相同，
-- 但是時間有交替或重復資料
and ((a.s_time between b.s_time and b.e_time) or
(a.e_time between b.s_time and b.e_time) )
)

[tim_spac]

select * from table a
where exists (select 1 from table b
  where b.mno=a.mo -- mo相同，
  -- 但是時間有交替或重復資料
  and ((a.s_time between b.s_time and b.e_time) or
       (a.e_time between b.s_time and b.e_time) )
  )

[tim_spac]

好像有书写错误。

select * from table a
where exists (select 1 from table b
  where b.mno=a.mo -- mno相同，          -- 上面的代码在这里有错误
  -- 但是時間有交替或重復資料
  and ((a.s_time between b.s_time and b.e_time) or
       (a.e_time between b.s_time and b.e_time) )
  )

[zzuyongp]

把我想的都写完了

[hedy821]

都看不懂楼主的问题,:-~

[ReyZhang]

学习啦!

[UltraBejing]

不会,帮顶

[utpcb]

create table tb(A_no varchar(10),mno varchar(10),s_time datetime,e_time datetime)
insert into tb values('B131', '彭秀瓊', '20080116', '20080117') 
insert into tb values('B131', '彭秀瓊', '20080107', '20080108') 
insert into tb values('B131', '彭秀瓊', '20080109', '20080119') 
insert into tb values('B465', '龍芙蓉', '20080109', '20080110') 
insert into tb values('b776', '陳愛儀', '20080301', '20080301') 
insert into tb values('B990', '張榮英', '20080109', '20080110') 
insert into tb values('C467', '張白芳', '20080109', '20080110') 
insert into tb values('C634', '趙本會', '20080109', '20080110') 
insert into tb values('C634', '趙本會', '20080107', '20080108') 
insert into tb values('C634', '趙本會', '20080112', '20080131') 
go

select id=identity(int,1,1) , * into tb1 from tb

SELECT TOP 8000 id = identity(int,0,1) INTO tb2 FROM syscolumns a, syscolumns b  

select distinct m.* from tb m,
(
  select mno , mytime from
  (
    select tb1.id , tb1.mno , dateadd(day , tb2.id , tb1.s_time) mytime from tb1 , tb2 where dateadd(day , tb2.id , tb1.s_time) <= tb1.e_time
  ) t
  group by mno , mytime having count(*) > 1
) n
where m.mno = n.mno and m.s_time <= n.mytime and m.e_time >= n.mytime

drop table tb,tb1,tb2

/*
A_no       mno        s_time                                                 e_time                                                 
---------- ---------- ------------------------------------------------------ ------------------------------------------------------ 
B131       彭秀瓊        2008-01-09 00:00:00.000                                2008-01-19 00:00:00.000
B131       彭秀瓊        2008-01-16 00:00:00.000                                2008-01-17 00:00:00.000

（所影响的行数为 2 行）
*/