案例:预订会议室时判断提交预订的时间段是否与已经预订的时间段冲突
CREATE TABLE roombookinfo(
id int NOT NULL,
title nvarchar(10) NOT NULL,
beginTime datetime NOT NULL,
endTime datetime NOT NULL
)
select * from roomBookInfo
truncate table roomBookInfo
INSERT INTO roomBookInfo VALUES (1, '会议一', '2010-10-15 00:01:00', '2010-10-15 00:03:00');
INSERT INTO roomBookInfo VALUES (2, '会议二', '2010-10-15 00:05:00', '2010-10-15 00:08:30');
INSERT INTO roomBookInfo VALUES (3, '会议三', '2010-10-16 00:12:00', '2010-10-17 00:15:00');
--网上找的解决方案
declare @bTime nvarchar(50)
declare @eTIme nvarchar(50)
set @bTime='2010-10-13 00:04:30'
set @eTIme='2010-10-18 00:07:30'
select * from roombookinfo where
((@bTime>=beginTime and @bTime<endTime) or(@bTime<endTime and @eTIme>endTime) or(@bTime<=beginTime and @eTIme>beginTime))
--根据网上打的资料自己写的(显然没有上面的精辟)
--declare@bTime nvarchar(50)
--declare@eTIme nvarchar(50)
set @bTime='2010-10-12 00:04:30'
set @eTIme='2010-10-18 00:09:30'
SELECT * FROM roombookinfo WHERE
(@bTime between begintime AND endTime) or(@eTIme between begintime AND endTime)
or(begintime between @bTime AND @eTIme) or(endTime between @bTime AND @eTIme)
--其它的用法
declare @a nvarchar(50)
declare @b nvarchar(50)
set @a='2010-10-15 00:04:30'
set @b='2010-10-17 00:09:30'
SELECT CASE(SELECT COUNT(*) FROM roombookinfo WHERE
(@a between begintime AND endTime) or(@b between begintime AND endTime)
or(begintime between @a AND @b) or(endTime between @a AND @b)
)
WHEN 0 THEN '会议未创建'
WHEN 1 THEN '有一个会议'
ELSE
'有很多重复的会议'
END