--建立测试环境 Create Table TEST (ID Int, StarTime1 char(10), EndTime1 char(10), StarTime2 char(10), EndTime2 char(10)) --插入数据 Insert TEST Values(1,'05:12:12','15:20:20','12:20:20','21:12:12') Insert TEST Values(2,'05:12:12','08:20:20','12:20:20','21:12:12') Insert TEST Values(3,'09:12:12','19:20:20','12:20:20','21:12:12') --测试 Select * from TEST Where (StarTime2 Between StarTime1 And EndTime1) --删除测试环境 Drop table TEST --结果 /* ID StarTime1 EndTime1 StarTime2 EndTime2 1 05:12:12 15:20:20 12:20:20 21:12:12 3 09:12:12 19:20:20 12:20:20 21:12:12 */
如果会出现后面的时间段大于前面的时间段,就加上一个条件--建立测试环境 Create Table TEST (ID Int, StarTime1 char(10), EndTime1 char(10), StarTime2 char(10), EndTime2 char(10)) --插入数据 Insert TEST Values(1,'05:12:12','15:20:20','12:20:20','21:12:12') Insert TEST Values(2,'05:12:12','08:20:20','12:20:20','21:12:12') Insert TEST Values(3,'19:12:12','22:20:20','12:20:20','21:12:12') --测试 Select * from TEST Where (StarTime2 Between StarTime1 And EndTime1) Or (EndTime2 Between StarTime1 And EndTime1) --删除测试环境 Drop table TEST --结果 /* ID StarTime1 EndTime1 StarTime2 EndTime2 1 05:12:12 15:20:20 12:20:20 21:12:12 3 19:12:12 22:20:20 12:20:20 21:12:12 */
Create Table TEST
(ID Int,
StarTime1 char(10),
EndTime1 char(10),
StarTime2 char(10),
EndTime2 char(10))
--插入数据
Insert TEST Values(1,'05:12:12','15:20:20','12:20:20','21:12:12')
Insert TEST Values(2,'05:12:12','08:20:20','12:20:20','21:12:12')
Insert TEST Values(3,'09:12:12','19:20:20','12:20:20','21:12:12')
--测试
Select * from TEST Where (StarTime2 Between StarTime1 And EndTime1)
--删除测试环境
Drop table TEST
--结果
/*
ID StarTime1 EndTime1 StarTime2 EndTime2
1 05:12:12 15:20:20 12:20:20 21:12:12
3 09:12:12 19:20:20 12:20:20 21:12:12
*/
Create Table TEST
(ID Int,
StarTime1 char(10),
EndTime1 char(10),
StarTime2 char(10),
EndTime2 char(10))
--插入数据
Insert TEST Values(1,'05:12:12','15:20:20','12:20:20','21:12:12')
Insert TEST Values(2,'05:12:12','08:20:20','12:20:20','21:12:12')
Insert TEST Values(3,'19:12:12','22:20:20','12:20:20','21:12:12')
--测试
Select * from TEST Where (StarTime2 Between StarTime1 And EndTime1) Or (EndTime2 Between StarTime1 And EndTime1)
--删除测试环境
Drop table TEST
--结果
/*
ID StarTime1 EndTime1 StarTime2 EndTime2
1 05:12:12 15:20:20 12:20:20 21:12:12
3 19:12:12 22:20:20 12:20:20 21:12:12
*/
用Between 的话,SQL回自动将StarTime2 它们转化成时间类型对吗 ?
完了立即结贴...再次感谢 paoluo(一天到晚游泳的鱼)