sqlserver問題,超多分...

2008-01-17 09:04:50 349 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Adminer 连接mssql sqlserver ./freetds/freetds.conf:/etc/freetds.conf:rw # 确保 :rw 可读写。Adminer 安装mssql sqlserver php 驱动；adminer 安装pdo_dblib mssql;这个版本的镜像不用自己安装驱动了。耗时一个美好的周六下午。阅读详情

數據庫表裡有如下數據:
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相同，但是時間有交替或重復資料呢？

...全文

在DJANGO中要连接mssql（sqlserver）如何配置,实测可用现在，你的Django项目已配置为使用MSSQL数据库。你可以在Django中使用ORM进行数据库操作，并与MSSQL进行交互。网上找了大量的资料测试过都解决不了django连接sqlserver的问题。设置中，设置MSSQL连接的相关配置。在DJANGO中要连接mssql如何配置。这些配置是可选的，根据你的需求进行设置。首先，确保你的Python环境中已安装。经过好几天公关终于解决了这个难题。打开Django项目中的。阅读详情

colinliu

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

utpcb 2008-04-30

打赏
举报

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 行）

*/

UltraBejing 2008-04-30

打赏
举报

不会,帮顶

ReyZhang 2008-02-04

打赏
举报

学习啦!

hedy821 2008-02-03

打赏
举报

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

zzuyongp 2008-01-27

打赏
举报

把我想的都写完了

tim_spac 2008-01-26

打赏
举报

好像有书写错误。

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

  )

tim_spac 2008-01-25

打赏
举报

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 2008-01-25

打赏
举报

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

wyb0026 2008-01-24

打赏
举报



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

JiangHongTao 2008-01-24

打赏
举报

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 2008-01-23

打赏
举报

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

yang_dgut 2008-01-23

打赏
举报

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

TNT_1st_excellence 2008-01-17

打赏
举报

楼上正解!

dawugui 2008-01-17

打赏
举报

[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]

dobear_0922 2008-01-17

打赏
举报

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

dobear_0922 2008-01-17

打赏
举报

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

昵称被占用了 2008-01-17

打赏
举报

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

)

pt1314917 2008-01-17

打赏
举报



这样？

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

colinliu 2008-01-17

打赏
举报

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
這兩個就等於是重復.

一者仁心 2008-01-17

打赏
举报



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

加载更多回复（7）

sqlserver問題,超多分...

相关推荐