<b>实现内容的时间交集计算的sql语句</b>[MSSQL防范]

sql语句可以实现大量我们需求的功效,下面介绍的sql语句可以实现内容的时间交集计算,但愿对您有所启迪.

假如在系统中有一张IM的登录记录表,里面是员工IM的登录时间,有登录时间,离线时间,最后活动时间.

因为员工上半时有大概会同时登录多个IM,此中的时间会有交集,因为要计算员工每天IM在线的工作时间,交集时间需求过滤掉.

直接在sql server中便可以对比便利的计算出相关内容.具体的直接上sql语句代码:

--便利演示,定义一个Source的表变量,BeginTime是登录时间,EndTime是离线时间  
declare @Source table(ID INT IDENTITY(1,1),BeginTime datetime, EndTime datetime)  
--定义输出后果表变量  
declare @Result table(id INT IDENTITY(1,1),BeginTime datetime, EndTime DATETIME,OnlineTime INT NULL)  
 
--插入演示数据  
insert into @Source values('2010-08-16 08:00:00','2010-08-16 08:40:00')  
insert into @Source values('2010-08-16 09:00:00','2010-08-16 11:30:00')  
insert into @Source values('2010-08-16 09:01:00','2010-08-16 11:32:00')  
insert into @Source values('2010-08-16 10:01:00','2010-08-16 12:01:00')  
insert into @Source values('2010-08-16 11:00:00','2010-08-16 12:05:00')  
insert into @Source values('2010-08-16 12:10:00','2010-08-16 13:40:00')  
insert into @Source values('2010-08-16 13:10:00','2010-08-16 14:15:00')  
insert into @Source values('2010-08-16 14:00:00','2010-08-16 16:32:00')  
insert into @Source values('2010-08-16 07:20:00','2010-08-16 16:40:00')  
insert into @Source values('2010-08-16 17:00:00','2010-08-16 20:32:00')  
insert into @Source values('2010-08-16 07:00:00','2010-08-16 07:18:00')  
insert into @Source values('2010-08-16 07:30:00','2010-08-16 08:20:00')  
 
--模拟数据库中的数据,插入一些其他时间的数据  
insert into @Source values('2010-08-15 08:00:00','2010-08-16 08:40:00')  
insert into @Source values('2010-08-17 09:00:00','2010-08-16 11:30:00')  
insert into @Source values('2010-08-14 09:01:00','2010-08-16 11:32:00')  
insert into @Source values('2010-08-13 10:01:00','2010-08-16 12:01:00')  
insert into @Source values('2010-08-17 11:00:00','2010-08-16 12:05:00')  
insert into @Source values('2010-08-17 12:10:00','2010-08-16 13:40:00')  
insert into @Source values('2010-08-17 13:10:00','2010-08-16 14:15:00')  
insert into @Source values('2010-08-17 14:00:00','2010-08-16 16:32:00')  
insert into @Source values('2010-08-15 07:20:00','2010-08-16 16:40:00')  
insert into @Source values('2010-08-13 17:00:00','2010-08-16 20:32:00')  
insert into @Source values('2010-08-12 07:00:00','2010-08-16 07:18:00')  
insert into @Source values('2010-08-11 07:30:00','2010-08-16 08:20:00')  
 
--定义需求用到的变量  
DECLARE @count INT,@ID INT,@begintime DATETIME,@endtime DATETIME,@Date DATE  
 
--假如需求指定计算的日期,可以设置日期值,不然为null会计算全部的时间  
SET @Date = '2010-08-16 07:30:00' 
 
--假如日期不为空,则排除Source中的垃圾数据  
IF @Date IS NOT NULL  
BEGIN  
    DELETE FROM @Source WHERE BeginTime < @Date OR BeginTime >= DATEADD(DAY,1,@Date)  
END  
 
--计算Count,以便举行遍历  
SELECT @count = COUNT(1) FROM @Source  
 
--遍历Source中的数据  
WHILE @count > 0  
BEGIN  
      
    --按照时间排序,每次取1条数据  
    SELECT TOP 1 @begintime = BeginTime,@endtime = EndTime ,@IDID = ID FROM @Source  
    ORDER BY BeginTime  
      
    --假如Result中没有记录,则直接插入,不然举行判断  
    IF NOT EXISTS(SELECT 1 FROM @Result)  
    BEGIN  
        insert into @Result values(@begintime,@endtime,NULL);  
    END  
    ELSE  
    BEGIN  
        --由于Source的数据是经过排序的,所以下面的插入已经无效,直接注释掉  
        --IF NOT EXISTS(SELECT 1 FROM @Result WHERE BeginTime < @endtime AND EndTime < @begintime)  
        --BEGIN  
        --    insert into @Result values(@begintime,@endtime,NULL);  
        --END  
          
        --假如开始时间大于Result中的已存在的记录完毕时间,则直接插入数据  
        IF NOT EXISTS(SELECT 1 FROM @Result WHERE EndTime > @begintime)  
        BEGIN  
            insert into @Result values(@begintime,@endtime,NULL);  
        END  
        --判断Result中的数据开始时间能否大于Source中的开始时间,大于则更新  
        ELSE IF EXISTS(SELECT 1 FROM @Result WHERE BeginTime > @begintime AND EndTime < @endtime AND EndTime > @endtime)  
        BEGIN  
            UPDATE @Result SET BeginTime = @begintime WHERE BeginTime > @begintime AND BeginTime < @endtime AND EndTime > @endtime  
        END  
        --判断Result中的数据完毕时间能否小于Source中的完毕时间,小于则更新  
        ELSE IF EXISTS(SELECT 1 FROM @Result WHERE BeginTime < @begintime AND BeginTime < @endtime AND EndTime < @endtime AND EndTime > @begintime)  
        BEGIN  
            UPDATE @Result SET EndTime = @endtime WHERE BeginTime < @begintime AND BeginTime < @endtime AND EndTime < @endtime AND EndTime > @begintime  
        END  
        --判断Result中的记录时间能否被Source中的时间被包含,包含则开始与完毕都更新  
        ELSE IF EXISTS(SELECT 1 FROM @Result WHERE BeginTime > @begintime AND BeginTime < @endtime AND EndTime < @endtime AND EndTime > @begintime)  
        BEGIN  
            SELECT * FROM @Result WHERE BeginTime > @begintime AND BeginTime < @endtime AND EndTime < @endtime AND EndTime > @begintime  
            UPDATE @Result SET BeginTime = @begintime, EndTime = @endtime WHERE BeginTime > @begintime AND BeginTime < @endtime AND EndTime < @endtime AND EndTime > @begintime  
        END  
    END  
      
    --删除已经读取过的记录  
    DELETE FROM @Source WHERE id = @ID  
    --更新Count  
    SELECT @count = COUNT(1) FROM @Source  
END  
 
--计算OnlineTime  
UPDATE @Result SET OnlineTime = DATEDIFF(mi,BeginTime,EndTime)  
--显示后果  
SELECT * FROM @Result