IF(OBJECT_ID('t_time') IS NOT NULL)
DROP TABLE t_time
CREATE TABLE t_time
(
begintime DATETIME,
endtime DATETIME
)
INSERT t_time
SELECT '2010-10-14 09:00:00','2010-10-14 10:00:00'
SELECT * FROM t_time
--以上表中有两条记录 我现在想 查询 某起始时间和结束时间 与这条记录的交集,若无交集则舍弃掉
--2010-10-14 08:00:00 2010-10-14 09:30:00
DECLARE @begintime1 DATETIME,@endtime1 DATETIME
--1. 当@begintime1='2010-10-14 08:00:00',@endtime1='2010-10-14 08:30:00' 时 无交集
--交集为 null
--1. 当@begintime1='2010-10-14 08:00:00',@endtime1='2010-10-14 09:30:00' 时 取左侧交集
--交集为 2010-10-14 09:00:00 2010-10-14 09:30:00
--2. 当@begintime1='2010-10-14 09:10:00',@endtime1='2010-10-14 09:50:00' 时 包含
--交集为 2010-10-14 09:10:00 2010-10-14 09:50:00
--2. 当@begintime1='2010-10-14 09:30:00',@endtime1='2010-10-14 10:30:00' 时 右侧交集
--交集为 2010-10-14 09:30:00 2010-10-14 10:00:00
--2. 当@begintime1='2010-10-14 08:30:00',@endtime1='2010-10-14 10:30:00' 时 被包含
--交集为 2010-10-14 09:00:00 2010-10-14 10:00:00
………………………… 请各位赐教。
DROP TABLE t_time
CREATE TABLE t_time
(
begintime DATETIME,
endtime DATETIME
)
INSERT t_time
SELECT '2010-10-14 09:00:00','2010-10-14 10:00:00'
SELECT * FROM t_time
--以上表中有两条记录 我现在想 查询 某起始时间和结束时间 与这条记录的交集,若无交集则舍弃掉
--2010-10-14 08:00:00 2010-10-14 09:30:00
DECLARE @begintime1 DATETIME,@endtime1 DATETIME
--1. 当@begintime1='2010-10-14 08:00:00',@endtime1='2010-10-14 08:30:00' 时 无交集
--交集为 null
--1. 当@begintime1='2010-10-14 08:00:00',@endtime1='2010-10-14 09:30:00' 时 取左侧交集
--交集为 2010-10-14 09:00:00 2010-10-14 09:30:00
--2. 当@begintime1='2010-10-14 09:10:00',@endtime1='2010-10-14 09:50:00' 时 包含
--交集为 2010-10-14 09:10:00 2010-10-14 09:50:00
--2. 当@begintime1='2010-10-14 09:30:00',@endtime1='2010-10-14 10:30:00' 时 右侧交集
--交集为 2010-10-14 09:30:00 2010-10-14 10:00:00
--2. 当@begintime1='2010-10-14 08:30:00',@endtime1='2010-10-14 10:30:00' 时 被包含
--交集为 2010-10-14 09:00:00 2010-10-14 10:00:00
………………………… 请各位赐教。
朋友, 我希望不使用 case when,通过其他的 方法来计算出来。
谢谢。
@begintime1 between begintime and endtime
or
@endtime1 between begintime and endtime
朋友 如果
开始时间:'2010-10-14 08:00:00'
结束时间:'2010-10-14 09:30:00'
那交集 就是 '2010-10-14 09:00:00'——'2010-10-14 09:30:00'了。
你的这个sql 不太正确。
select max(begintime, @begintime1), min(endtime, @endtime1) from t_time
where (@begintime1<=@endtime1) and
(
@begintime1 between begintime and endtime
or
@endtime1 between begintime and endtime
)
select case when greatest(begintime,@begintime1)>least(endtime,@endtime1)
then NULL else greatest(begintime,@begintime1) end as @begintime1,
case when greatest(begintime,@begintime1)>least(endtime,@endtime1)
then NULL else least(endtime,@endtime1) end as @endtime1
from t_time
select @begintime1,@endtime1
from t_time where greatest(begintime,@begintime1)<=least(endtime,@endtime1)
'greatest' 不是可以识别的 内置函数名称。
朋友 我的是 sql 2005数据库,
你的这些 方法是 自定义的还是其他的?
汗- -!
那个mysql是支持的,有现成的函数
T-sql貌似没有啊,没见过,那就创建一个函数吧
( @Value1 datetime,
@Value2 datetime
)
RETURNS datetime
AS
BEGIN
DECLARE @ReturnValue datetime
SELECT @ReturnValue = case when @Value1<@Value2 then @Value2 else @Value1 end
RETURN @ReturnValue
ENDCREATE FUNCTION dbo.fnLeast
( @Value1 datetime,
@Value2 datetime
)
RETURNS datetime
AS
BEGIN
DECLARE @ReturnValue datetime
SELECT @ReturnValue = case when @Value1<@Value2 then @Value1 else @Value2 end
RETURN @ReturnValue
END
GO
from t_time
where case when begintime<@begintime1 then @begintime1 else begintime end<=
case when endtime<@endtime1 then endtime else @endtime1 end
IF(OBJECT_ID('t_time') IS NOT NULL)
DROP TABLE t_time
CREATE TABLE t_time
(
begintime DATETIME,
endtime DATETIME
)
INSERT t_time
SELECT '2010-10-14 09:00:00','2010-10-14 10:00:00'
--以上表中有两条记录 我现在想 查询 某起始时间和结束时间 与这条记录的交集,若无交集则舍弃掉
--2010-10-14 08:00:00 2010-10-14 09:30:00
DECLARE @begintime1 DATETIME,@endtime1 DATETIME
set @begintime1='2010-10-14 08:00:00'
set @endtime1='2010-10-14 09:30:00'
select case when begintime<@begintime1 then @begintime1 else begintime end as begintime1,
case when endtime<@endtime1 then endtime else @endtime1 end as endtime1
from t_time
where case when begintime<@begintime1 then @begintime1 else begintime end<=
case when endtime<@endtime1 then endtime else @endtime1 end
-------------------------begintime1 endtime1
2010-10-14 09:00:00.000 2010-10-14 09:30:00.000
case when begintime>=bt then begintime else bt end begintime,
case when endtime<=et then endtime else bt end endtime
from
t_time a
left join
(select @begintime1 bt, @endtime1 et) b
on bt<=et and (begintime between bt and et or endtime between bt and et)
--少了一点,总结一下
--方法一:
--先建函数,函数见#13
select dbo.fnGreatest(begintime,@begintime1),dbo.fnLeast(endtime,@endtime1)
from t_time where dbo.fnGreatest(begintime,@begintime1)<=dbo.fnLeast(endtime,@endtime1)--方法二:
--用case when
select case when begintime<@begintime1 then @begintime1 else begintime end as begintime1,
case when endtime<@endtime1 then endtime else @endtime1 end as endtime1
from t_time
where case when begintime<@begintime1 then @begintime1 else begintime end<=
case when endtime<@endtime1 then endtime else @endtime1 end
--其实原理一下
--或者 where条件可以不用case when
where @begintime1<=@endtime1
and
(@begintime1 between begintime and endtime
or
@endtime1 between begintime and endtime)
DROP TABLE t_time
CREATE TABLE t_time
(
begintime DATETIME,
endtime DATETIME
)
INSERT t_time
SELECT '2010-10-14 09:00:00','2010-10-14 10:00:00'
SELECT * FROM t_time
--以上表中有两条记录 我现在想 查询 某起始时间和结束时间 与这条记录的交集,若无交集则舍弃掉
--2010-10-14 08:00:00 2010-10-14 09:30:00
DECLARE @begintime1 DATETIME,@endtime1 DATETIME select begintime=case when datadiff(second,@begintime1,begintime)>0 and datadiff(second,@begintime1,endtime)<0 then null>
when datadiff(second,@begintime1,begintime)<0 and datadiff(second,@begintime1,endtime)>0 then @begintime1
when datadiff(second,@begintime1,begintime)>0 and datadiff(second,@begintime1,endtime)>0 then begintime
when datadiff(second,@begintime1,begintime)>0 and datadiff(second,@begintime1,endtime)>0 then begintime
end case ,
endtime=case when datadiff(second,@begintime1,begintime)>0 and datadiff(second,@begintime1,endtime)<0 then null>
when datadiff(second,@begintime1,begintime)<0 and datadiff(second,@endtime1,endtime)>0 then endtime
when datadiff(second,@begintime1,begintime)>0 and datadiff(second,@endtime1,endtime)>0 then @endtime1
when datadiff(second,@begintime1,begintime)>0 and datadiff(second,@endtime1,endtime)>0 then @endtime1
end case ,
from tb
这样 及更方面了
DECLARE @begintime1 DATETIME,@endtime1 DATETIME
select @begintime1='2010-10-14 09:10:00',@endtime1='2010-10-14 10:30:00' select case when begintime<@begintime1 then @begintime1 else begintime end as begintime1,
case when endtime<@endtime1 then endtime else @endtime1 end as endtime1
from t_time WHERE @endtime1>=begintime AND @begintime1<=endtime