select A.examDate,A.examTime,A.enroltype, 开始时间=dateadd(mi,left(A.examtime,charindex(':',A.examtime)-1)*60+ right(left(A.examtime,charindex('--',A.examtime)-1),2),A.ExamDate) , 结束时间= dateadd(mi,left(right(A.examtime,5),2)*60+right(A.examtime,2),A.ExamDate) ,B.examDate,B.examTime,B.enroltype, 开始时间=dateadd(mi,left(B.examtime,charindex(':',B.examtime)-1)*60+ right(left(B.examtime,charindex('--',B.examtime)-1),2),B.ExamDate) , 结束时间= dateadd(mi,left(right(B.examtime,5),2)*60+right(B.examtime,2),B.ExamDate) from #exam A, #exam B WHERE A.examDate = B.examDate AND A.enroltype <> B.enroltype AND dateadd(mi,left(A.examtime,charindex(':',A.examtime)-1)*60+ right(left(A.examtime,charindex('--',A.examtime)-1),2),A.ExamDate) > dateadd(mi,left(B.examtime,charindex(':',B.examtime)-1)*60+ right(left(B.examtime,charindex('--',B.examtime)-1),2),B.ExamDate) AND (dateadd(mi,left(A.examtime,charindex(':',A.examtime)-1)*60+ right(left(A.examtime,charindex('--',A.examtime)-1),2),A.ExamDate) BETWEEN dateadd(mi,left(B.examtime,charindex(':',B.examtime)-1)*60+ right(left(B.examtime,charindex('--',B.examtime)-1),2),B.ExamDate) AND dateadd(mi,left(right(B.examtime,5),2)*60+right(B.examtime,2),B.ExamDate) OR dateadd(mi,left(right(A.examtime,5),2)*60+right(A.examtime,2),A.ExamDate) BETWEEN dateadd(mi,left(B.examtime,charindex(':',B.examtime)-1)*60+ right(left(B.examtime,charindex('--',B.examtime)-1),2),B.ExamDate) AND dateadd(mi,left(right(B.examtime,5),2)*60+right(B.examtime,2),B.ExamDate) ) order by dateadd(mi,left(A.examtime,charindex(':',A.examtime)-1)*60+ right(left(A.examtime,charindex('--',A.examtime)-1),2),A.ExamDate)
create table #exam ( examDate datetime, examTime nvarchar(50), enroltype nvarchar(50) ) insert into #exam select '2011-07-09 00:00:00.000','14:00--16:30','语文' union all select '2011-07-09 00:00:00.000','9:00--10:30','数学' union all select '2011-07-09 00:00:00.000','11:00--12:30','英语' union all select '2011-07-09 00:00:00.000','17:30--19:00','物理' union all select '2011-07-09 00:00:00.000','17:00--18:00','生物' union all select '2011-07-09 00:00:00.000','11:30--12:00','化学' ;with f as ( select examDate, PARSENAME(replace(examTime,'--','.'),2) as btime, PARSENAME(replace(examTime,'--','.'),1) as etime, examTime, enroltype from #exam ), f1 as ( select px=ROW_NUMBER()over(order by examTime),* from f )--select * from f1 select a.* from f1 a,f1 b where a.btime<=b.etime and a.examDate=b.examDate and a.px=b.px-1
drop table #exam
create table #exam ( examDate datetime, examTime nvarchar(50), enroltype nvarchar(50) ) insert into #exam select '2011-07-09 00:00:00.000','14:00--16:30','语文' union all select '2011-07-09 00:00:00.000','9:00--10:30','数学' union all select '2011-07-09 00:00:00.000','11:00--12:30','英语' union all select '2011-07-09 00:00:00.000','17:30--19:00','物理' union all select '2011-07-09 00:00:00.000','17:00--18:00','生物' union all select '2011-07-09 00:00:00.000','11:30--12:00','化学' ;with f as ( select examDate, PARSENAME(replace(examTime,'--','.'),2) as btime, PARSENAME(replace(examTime,'--','.'),1) as etime, examTime, enroltype from #exam ), f1 as ( select px=ROW_NUMBER()over(order by examTime),* from f )--select * from f1 select a.* from f1 a,f1 b where a.btime<=b.etime and a.examDate=b.examDate and a.px=b.px-1
drop table #exam
create table #exam ( examDate datetime, examTime nvarchar(50), enroltype nvarchar(50) ) insert into #exam select '2011-07-09 00:00:00.000','14:00--16:30','语文' union all select '2011-07-09 00:00:00.000','9:00--10:30','数学' union all select '2011-07-09 00:00:00.000','11:00--12:30','英语' union all select '2011-07-09 00:00:00.000','17:30--19:00','物理' union all select '2011-07-09 00:00:00.000','17:00--18:00','生物' union all select '2011-07-09 00:00:00.000','11:30--12:00','化学' ;with f as ( select examDate, PARSENAME(replace(examTime,'--','.'),2) as btime, PARSENAME(replace(examTime,'--','.'),1) as etime, examTime, enroltype from #exam ), f1 as ( select px=ROW_NUMBER()over(order by examTime),* from f )--select * from f1 select a.* from f1 a,f1 b where a.btime<=b.etime and a.examDate=b.examDate and a.px=b.px-1
drop table #exam
create table #exam ( examDate datetime, examTime nvarchar(50), enroltype nvarchar(50) )insert into #exam select '2011-07-09 00:00:00.000','14:00--16:30','语文' union all select '2011-07-09 00:00:00.000','9:00--10:30','数学' union all select '2011-07-09 00:00:00.000','11:00--12:30','英语' union all select '2011-07-09 00:00:00.000','17:30--19:00','物理' union all select '2011-07-09 00:00:00.000','17:00--18:00','生物' union all select '2011-07-09 00:00:00.000','11:30--12:00','化学'with t as (select enroltype, convert(datetime,convert(varchar,examDate,23)+' '+left(examTime,charindex('-',examTime)-1)) bt, convert(datetime,convert(varchar,examDate,23)+' '+substring(examTime,charindex('--',examTime)+2,5)) et from #exam) select distinct t1.* from t t1 cross join t t2 where (t1.bt between t2.bt and t2.et or t2.bt between t1.bt and t1.et) and t1.enroltype<>t2.enroltype order by t1.btenroltype bt et --------------- ----------------------- -------------------- 英语 2011-07-09 11:00:00 2011-07-09 12:30:00 化学 2011-07-09 11:30:00 2011-07-09 12:00:00 生物 2011-07-09 17:00:00 2011-07-09 18:00:00 物理 2011-07-09 17:30:00 2011-07-09 19:00:00
-- 这样也许好点create table #exam ( examDate datetime, examTime nvarchar(50), enroltype nvarchar(50) ) insert into #exam select '2011-07-09 00:00:00.000','14:00--16:30','语文' union all select '2011-07-09 00:00:00.000','9:00--10:30','数学' union all select '2011-07-09 00:00:00.000','11:00--12:30','英语' union all select '2011-07-09 00:00:00.000','17:30--19:00','物理' union all select '2011-07-09 00:00:00.000','17:00--18:00','生物' union all select '2011-07-09 00:00:00.000','11:30--12:00','化学' ;with f as ( select examDate, PARSENAME(replace(examTime,'--','.'),2) as btime, PARSENAME(replace(examTime,'--','.'),1) as etime, examTime, enroltype from #exam ), select a.examDate,a.examTime,a.enroltype from f a,f b where a.btime<=b.etime and a.btime<=b.btime and a.examDate=b.examDate and a.enroltype=b.enroltype drop table #exam
create table #exam ( examDate datetime, examTime nvarchar(50), enroltype nvarchar(50) )insert into #exam select '2011-07-09 00:00:00.000','14:00--16:30','语文' union all select '2011-07-09 00:00:00.000','9:00--10:30','数学' union all select '2011-07-09 00:00:00.000','11:00--12:30','英语' union all select '2011-07-09 00:00:00.000','17:30--19:00','物理' union all select '2011-07-09 00:00:00.000','17:00--18:00','生物' union all select '2011-07-09 00:00:00.000','11:30--12:00','化学';with c as ( Select ROW_NUMBER() over(order by examDate) as FreshID, Substring(examTime,1,CHARINDEX('--',examTime)-1) as startTime, SUBSTRING(examTime,CHARINDEX('--',examTime)+2,LEN(examTime)-CHARINDEX('--',examTime)+2) as endTime, enroltype from #exam
) select cast(c1.FreshID as varchar(10))+' conflict '+ cast(c2.FreshID as varchar(10)), c1.enroltype +' conflict '+ c2.enroltype from c c1 cross join c c2 where ( (c1.startTime between c2.startTime and c2.endTime) or (c1.endTime between c2.startTime and c2.endTime) or (c2.startTime between c1.startTime and c1.endTime) or (c2.endTime between c1.startTime and c1.endTime) ) and c1.FreshID<>c2.FreshID and c1.FreshID>c2.FreshIDdrop table #exam go借用下唐诗的数据 呵……
开始时间=dateadd(mi,left(A.examtime,charindex(':',A.examtime)-1)*60+
right(left(A.examtime,charindex('--',A.examtime)-1),2),A.ExamDate) ,
结束时间= dateadd(mi,left(right(A.examtime,5),2)*60+right(A.examtime,2),A.ExamDate)
,B.examDate,B.examTime,B.enroltype,
开始时间=dateadd(mi,left(B.examtime,charindex(':',B.examtime)-1)*60+
right(left(B.examtime,charindex('--',B.examtime)-1),2),B.ExamDate) ,
结束时间= dateadd(mi,left(right(B.examtime,5),2)*60+right(B.examtime,2),B.ExamDate)
from #exam A, #exam B
WHERE A.examDate = B.examDate
AND A.enroltype <> B.enroltype
AND dateadd(mi,left(A.examtime,charindex(':',A.examtime)-1)*60+
right(left(A.examtime,charindex('--',A.examtime)-1),2),A.ExamDate)
>
dateadd(mi,left(B.examtime,charindex(':',B.examtime)-1)*60+
right(left(B.examtime,charindex('--',B.examtime)-1),2),B.ExamDate)
AND (dateadd(mi,left(A.examtime,charindex(':',A.examtime)-1)*60+
right(left(A.examtime,charindex('--',A.examtime)-1),2),A.ExamDate)
BETWEEN dateadd(mi,left(B.examtime,charindex(':',B.examtime)-1)*60+
right(left(B.examtime,charindex('--',B.examtime)-1),2),B.ExamDate)
AND dateadd(mi,left(right(B.examtime,5),2)*60+right(B.examtime,2),B.ExamDate)
OR dateadd(mi,left(right(A.examtime,5),2)*60+right(A.examtime,2),A.ExamDate)
BETWEEN dateadd(mi,left(B.examtime,charindex(':',B.examtime)-1)*60+
right(left(B.examtime,charindex('--',B.examtime)-1),2),B.ExamDate)
AND dateadd(mi,left(right(B.examtime,5),2)*60+right(B.examtime,2),B.ExamDate)
)
order by dateadd(mi,left(A.examtime,charindex(':',A.examtime)-1)*60+
right(left(A.examtime,charindex('--',A.examtime)-1),2),A.ExamDate)
然后如果取出两天数据,那就是有冲突
(
examDate datetime,
examTime nvarchar(50),
enroltype nvarchar(50)
)
insert into #exam
select '2011-07-09 00:00:00.000','14:00--16:30','语文'
union all
select '2011-07-09 00:00:00.000','9:00--10:30','数学'
union all
select '2011-07-09 00:00:00.000','11:00--12:30','英语'
union all
select '2011-07-09 00:00:00.000','17:30--19:00','物理'
union all
select '2011-07-09 00:00:00.000','17:00--18:00','生物'
union all
select '2011-07-09 00:00:00.000','11:30--12:00','化学'
;with f as
(
select
examDate,
PARSENAME(replace(examTime,'--','.'),2) as btime,
PARSENAME(replace(examTime,'--','.'),1) as etime,
examTime,
enroltype
from
#exam
),
f1 as
(
select px=ROW_NUMBER()over(order by examTime),* from f
)--select * from f1
select
a.*
from
f1 a,f1 b
where
a.btime<=b.etime
and
a.examDate=b.examDate
and
a.px=b.px-1
drop table #exam
(
examDate datetime,
examTime nvarchar(50),
enroltype nvarchar(50)
)
insert into #exam
select '2011-07-09 00:00:00.000','14:00--16:30','语文'
union all
select '2011-07-09 00:00:00.000','9:00--10:30','数学'
union all
select '2011-07-09 00:00:00.000','11:00--12:30','英语'
union all
select '2011-07-09 00:00:00.000','17:30--19:00','物理'
union all
select '2011-07-09 00:00:00.000','17:00--18:00','生物'
union all
select '2011-07-09 00:00:00.000','11:30--12:00','化学'
;with f as
(
select
examDate,
PARSENAME(replace(examTime,'--','.'),2) as btime,
PARSENAME(replace(examTime,'--','.'),1) as etime,
examTime,
enroltype
from
#exam
),
f1 as
(
select px=ROW_NUMBER()over(order by examTime),* from f
)--select * from f1
select
a.*
from
f1 a,f1 b
where
a.btime<=b.etime
and
a.examDate=b.examDate
and
a.px=b.px-1
drop table #exam
(
examDate datetime,
examTime nvarchar(50),
enroltype nvarchar(50)
)
insert into #exam
select '2011-07-09 00:00:00.000','14:00--16:30','语文'
union all
select '2011-07-09 00:00:00.000','9:00--10:30','数学'
union all
select '2011-07-09 00:00:00.000','11:00--12:30','英语'
union all
select '2011-07-09 00:00:00.000','17:30--19:00','物理'
union all
select '2011-07-09 00:00:00.000','17:00--18:00','生物'
union all
select '2011-07-09 00:00:00.000','11:30--12:00','化学'
;with f as
(
select
examDate,
PARSENAME(replace(examTime,'--','.'),2) as btime,
PARSENAME(replace(examTime,'--','.'),1) as etime,
examTime,
enroltype
from
#exam
),
f1 as
(
select px=ROW_NUMBER()over(order by examTime),* from f
)--select * from f1
select
a.*
from
f1 a,f1 b
where
a.btime<=b.etime
and
a.examDate=b.examDate
and
a.px=b.px-1
drop table #exam
create table #exam
(
examDate datetime,
examTime nvarchar(50),
enroltype nvarchar(50)
)insert into #exam
select '2011-07-09 00:00:00.000','14:00--16:30','语文'
union all
select '2011-07-09 00:00:00.000','9:00--10:30','数学'
union all
select '2011-07-09 00:00:00.000','11:00--12:30','英语'
union all
select '2011-07-09 00:00:00.000','17:30--19:00','物理'
union all
select '2011-07-09 00:00:00.000','17:00--18:00','生物'
union all
select '2011-07-09 00:00:00.000','11:30--12:00','化学'with t as
(select enroltype,
convert(datetime,convert(varchar,examDate,23)+' '+left(examTime,charindex('-',examTime)-1)) bt,
convert(datetime,convert(varchar,examDate,23)+' '+substring(examTime,charindex('--',examTime)+2,5)) et
from #exam)
select distinct t1.*
from t t1
cross join t t2
where (t1.bt between t2.bt and t2.et
or t2.bt between t1.bt and t1.et)
and t1.enroltype<>t2.enroltype
order by t1.btenroltype bt et
--------------- ----------------------- --------------------
英语 2011-07-09 11:00:00 2011-07-09 12:30:00
化学 2011-07-09 11:30:00 2011-07-09 12:00:00
生物 2011-07-09 17:00:00 2011-07-09 18:00:00
物理 2011-07-09 17:30:00 2011-07-09 19:00:00
我直接拿着语句去sql里运行- -,
消息 156,级别 15,状态 1,第 19 行
在关键字 'with' 附近有语法错误。
消息 170,级别 15,状态 1,第 29 行
第 29 行: ',' 附近有语法错误。
消息 195,级别 15,状态 10,第 32 行
'ROW_NUMBER' 不是可以识别的 函数名。
是不是因为我的是2000数据库的问题?你的语句很精简,但这个with as 语句我是第一次看到。 学习了。。 呵呵
能问下,sql2005里您的语句可以测试成功吗?
(
examDate datetime,
examTime nvarchar(50),
enroltype nvarchar(50)
)
insert into #exam
select '2011-07-09 00:00:00.000','14:00--16:30','语文'
union all
select '2011-07-09 00:00:00.000','9:00--10:30','数学'
union all
select '2011-07-09 00:00:00.000','11:00--12:30','英语'
union all
select '2011-07-09 00:00:00.000','17:30--19:00','物理'
union all
select '2011-07-09 00:00:00.000','17:00--18:00','生物'
union all
select '2011-07-09 00:00:00.000','11:30--12:00','化学'
;with f as
(
select
examDate,
PARSENAME(replace(examTime,'--','.'),2) as btime,
PARSENAME(replace(examTime,'--','.'),1) as etime,
examTime,
enroltype
from
#exam
),
select
a.examDate,a.examTime,a.enroltype
from
f a,f b
where
a.btime<=b.etime
and
a.btime<=b.btime
and
a.examDate=b.examDate
and
a.enroltype=b.enroltype
drop table #exam
create table #exam
(
examDate datetime,
examTime nvarchar(50),
enroltype nvarchar(50)
)insert into #exam
select '2011-07-09 00:00:00.000','14:00--16:30','语文'
union all
select '2011-07-09 00:00:00.000','9:00--10:30','数学'
union all
select '2011-07-09 00:00:00.000','11:00--12:30','英语'
union all
select '2011-07-09 00:00:00.000','17:30--19:00','物理'
union all
select '2011-07-09 00:00:00.000','17:00--18:00','生物'
union all
select '2011-07-09 00:00:00.000','11:30--12:00','化学';with c as
(
Select ROW_NUMBER() over(order by examDate) as FreshID,
Substring(examTime,1,CHARINDEX('--',examTime)-1) as startTime,
SUBSTRING(examTime,CHARINDEX('--',examTime)+2,LEN(examTime)-CHARINDEX('--',examTime)+2) as endTime,
enroltype
from #exam
)
select cast(c1.FreshID as varchar(10))+' conflict '+ cast(c2.FreshID as varchar(10)),
c1.enroltype +' conflict '+ c2.enroltype
from c c1
cross join c c2
where ( (c1.startTime between c2.startTime and c2.endTime) or (c1.endTime between c2.startTime and c2.endTime)
or (c2.startTime between c1.startTime and c1.endTime) or (c2.endTime between c1.startTime and c1.endTime) )
and c1.FreshID<>c2.FreshID and c1.FreshID>c2.FreshIDdrop table #exam
go借用下唐诗的数据 呵……