---原帖地址:(是Oracle的)
http://topic.csdn.net/u/20090923/13/baaa34b4-184d-4805-b6d4-747ec0845fe6.html?10721
--但我觉得这个题目非常值得大家一做,大家有什么更好的方法,欢迎建议!谢谢!
--现在有这样的一个问题
--有这样的一个表hist 表里有四个字段
-- 一个是id (行号id),一个是effdat(登记开始的时间),一个是expdat(登记结束时间),最后一个是userid(用户ID)
--一个人可能有多次登记 ,而且登记的时间可能是包含的关系。也可能是不连续的。
--现在就是判断都有那些人在一个特定的时间段内登记是连续的。
--比如说从‘2005-01-01’到‘2006-01-01’ 下面是一段查询的记录 select * from hist
SQL code
1 2005-6-14 2005-8-31 -32169
2 2005-7-1 2005-8-31 -32169
3 2005-9-1 2005-12-31 -32169
4 2006-1-1 2006-3-31 -32169
5 2006-1-1 2006-8-31 -32169
6 2006-9-1 2006-9-30 -32169
7 2006-10-1 2006-12-31 -32169
8 2005-6-21 2005-6-30 -32203
9 2005-7-1 2005-12-31 -32203
10 2005-7-1 2006-3-31 -32203
11 2005-7-1 2006-6-30 -32203
12 2006-7-1 2006-12-31 -32203
13 2005-7-3 2005-12-31 -32318
14 2005-7-3 2006-2-28 -32318
15 2006-3-1 2006-3-31 -32318
16 2006-3-1 2006-12-31 -32318
17 2005-7-7 2005-12-31 -32396
18 2005-7-7 2006-3-31 -32396
19 2005-7-7 2006-6-30 -32396
20 2006-7-1 2006-11-15 -32396
21 2005-7-13 2005-12-31 -32501
22 2006-1-1 2006-3-31 -32501
23 2006-1-1 2006-12-31 -32501
--大家看看这个查询的结果 我解释一下 第一个1 那是行号
--后面的2005-6-14 是登记的开始时间,2005-8-31是登记的结束时间,意思就是他在2005-6-14 到 2005-8-31 是连续登记的。
--后面的-32169 是人的id。
--但是第二条记录的开始时间和登记的结束时间分别是 2005-7-1 2005-8-31 这个包含在第一个时间里面,说明他在2005-6-14 2005-8-31是连续的。
--现在我就想求各位帮我写一个方法。无论是通过什么方法。可以是程序(限pb程序)能够求出这个表内在某个时间段内哪些人是连续登记的……
求求各位了……………………首先谢谢各位!!
-----------------------------------------------------------------------------------------------------------------------------
我们得先明白:我们到底怎么选择时间区间? ----比如说:我们要选择在 2006-01-01(起始时间段) 到 2006-11-12(结束时间段)这段时间内的连续用户
----是不是表里面的每条记录应该满足:effdat <='2006-11-12' and expdat>='2006-01-01' --------------------请仔细想想!
----每行记录的effdat应该小于等于 2006-11-12(结束时间段),并且:expdat应该大于等于2006-01-01(起始时间段),
----各位:好好想想:我说的对吗? 比如说:用户-32396在表中共有以下四条记录,
1 2005-7-7 2005-12-31 -32396
2 2005-11-7 2006-3-31 -32396
3 2006-4-7 2006-6-30 -32396
4 2006-6-1 2006-11-15 -32396
我要求解:用户在2006-01-01到2006-11-12日,是否是连续的,我们应该只考虑2、3、4三条记录就够了(正如我上面所说的) 3. 用户Id不是序列Id,是后面的-32169.....才是真正的用户ID,楼主:对吧?
---当然:这一点好多人没注意,迷惑了! 4. 整了几天:各位还没有把连续的概念给整明白:
比如说:下面四条记录:
1 2005-7-7 2005-12-31 -32396
2 2005-11-7 2006-3-31 -32396
3 2006-4-7 2006-6-30 -32396
4 2006-6-1 2006-11-15 -32396
第一条记录:无可否认:表示用户从2005-7-7 到 2005-12-31 是连续的, 当在判断第二记录(前提:userid得与前一条记录的userid相等)时,
我们先把第一记录的effdat看作oldeffdat,expdat看作oldexpdat(可以理解吧) 如果它后面存在任意一条记录,这条的effdat看作neweffdat,expdat看作newexpdat(可以理解吧) 此时:记录存在三种情况:
1: neweffdat 介于(可以等于)先前一条记录的effdat(oldeffdat) 与 expdat(oldffdat) 之间,
并且,newexpdat > oldexpdat
那么这个用户在时间区间 oldeffdat与newexpdat之间是连续的 2:newexpdat介于(可以等于)先前一条记录的effdat(oldeffdat) 与 expdat(oldffdat) 之间,
并且,neweffdat < oldeffdat
那么这个用户在时间区间 neweffdat与oldexpdat之间是连续的 3:neweffdat <= 先前一条记录的effdat(oldeffdat),并且 newexpdat >= 先前一条记录的expdat(oldeffdat)
也就是说先前一条记录的时间区间真包含于新记录的时间区间,
此时:那么说明这个用户在时间区间:neweffdat与newexpdat之间是连续的。 --同样的判断用于第一条记录后面的任意记录,依此类推! 不知道我说的,大家能不能明白,也不知道楼主是不是这个意思?
---------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[sequen_time] @startdate DATETIME, @enddate DATETIME
AS
/*
---时间段连续问题(SQL Server终结、优化版)
SELECT * FROM hist WHERE effdat<='20070101' and expdat>='20050707' order by effdat;
EXEC sequen_time '2005-07-01','2006-01-24'
*/
BEGIN
--select convert(datetime,'20061125',121) --如果输入(起始时间断)参数为空,则判断近半个月以来的连续用户
IF (ISNULL(@startdate,'')='')
SET @startdate=CONVERT(CHAR(10),GETDATE()-15,121)
IF (ISNULL(@enddate,'')='')
SET @enddate=CONVERT(CHAR(10),GETDATE(),121) DECLARE @count1 INT --用以查看是否有符合给定日期条件的记录
DECLARE @count2 INT --用以查看是否有符合给定日期条件的记录输出
DECLARE @count3 INT --用以查看是否有符合给定日期条件的记录输出 SELECT @count1=COUNT(Id) FROM hist
WHERE effdat<=@enddate AND expdat>=@startdate IF(@count1>0) --如果:有符合给定日期条件的记录
BEGIN CREATE TABLE #DBTempA(
Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userID VARCHAR(20) ) INSERT INTO #DBTempA(effdat,expdat,userID)
SELECT effdat,expdat,userID FROM hist
WHERE effdat<=@enddate AND expdat>=@startdate --用以保存符合给定时间段要求的用户记录
CREATE TABLE #DBTempB(
Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userID VARCHAR(20) ) --如果存在直接记录行符合给定日期条件:
INSERT INTO #DBTempB(effdat, expdat, userID)
SELECT effdat,expdat,userID FROM #DBTempA
WHERE effdat<=CONVERT(CHAR(10),@startdate,121) AND expdat>=CONVERT(CHAR(10),@enddate,121); --删除已经存在直接记录符合指定时间段的用户
DELETE FROM #DBTempA WHERE userID IN (SELECT userID FROM #DBTempB); --记录将要判断的记录行数,用以每次循环后递减,当@count为0时,退出While循环
SELECT @count2=COUNT(Id) FROM #DBTempA; WHILE(@count2>0) --如果还有记录:游标判断间接符合条件的记录
BEGIN DECLARE @userID INT --保存用户名,在游标中将根据用户名选择记录进行循环判断
DECLARE @oldID INT --记下ID,用以游标完成一次循环后删除记录
DECLARE @newID INT --记下ID,用以游标完成一次循环后删除记录
DECLARE @oldStartdate DATETIME
DECLARE @oldEnddate DATETIME
DECLARE @newStartdate DATETIME
DECLARE @newEnddate DATETIME SELECT TOP 1 @oldId=Id, @oldStartdate=effdat, @oldEnddate=expdat, @userID=userId FROM #DBTempA DECLARE @MyData CURSOR
SET @MyData = CURSOR FOR
SELECT effdat, expdat FROM #DBTempA WHERE userId=LTRIM(RTRIM(@userID)) AND Id<>@oldId
OPEN @MyData
FETCH next FROM @MyData INTO @newStartdate,@newEnddate
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@newStartdate>=@oldStartdate AND @newStartdate<=@oldEnddate AND @newEnddate>@oldEnddate)
BEGIN
SET @oldEnddate=@newEnddate;
END
IF(@newEnddate<=@oldEnddate AND @newEnddate>=@oldStartdate AND @newStartdate<@oldStartdate)
BEGIN
SET @oldStartdate=@newStartdate;
END
IF(@newStartdate<@oldStartdate AND @newEnddate>@oldEnddate )
BEGIN
SET @oldStartdate=@newStartdate;
SET @oldEnddate=@newEnddate;
END
FETCH next FROM @MyData INTO @newStartdate, @newEnddate
END
CLOSE @MyData
DEALLOCATE @MyData --如果找到符合条件的记录
IF (@oldStartdate<=@startdate AND @oldEnddate>=@enddate)
BEGIN
INSERT INTO #DBTempB(effdat, expdat, userID) SELECT @oldStartdate,@oldEnddate,@userId;
DELETE FROM #DBTempA WHERE userID=@userID;
SELECT @count2=COUNT(Id) FROM #DBTempA; --重置记数变量
END
ELSE
BEGIN
DELETE FROM #DBTempA WHERE userID=@userID;
SELECT @count2=COUNT(Id) FROM #DBTempA; --重置记数变量
END
END SELECT @count3=COUNT(ID) FROM #DBTempB;
IF(@count3>0)
SELECT * FROM #DBTempB;
ELSE
SELECT '对不起:没有符合要求的给定时间段内连续的记录!' '无符符合要求记录';
DROP TABLE #DBTempA,#DBTempB;
END ELSE
SELECT '对不起:没有您要的给定时间段内连续的记录!' '无符合初始条件记录';END
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
-------------------------------------------------------------------------------------------------建表,插入数据
CREATE TABLE hist(Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userId VARCHAR(10) )
INSERT INTO hist(effdat,expdat,userID)
VALUES('2005-6-14','2005-8-31','-32169');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-1','2005-8-31','-32169');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-9-1','2005-12-31','-32169');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-1-1','2006-3-31','-32169');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-1-1','2006-8-31','-32169');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-9-1','2006-9-30','-32169');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-10-1','2006-12-31','-32169');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-6-21','2005-6-30','-32203');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-1','2005-12-31','-32203');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-1','2006-3-31','-32203');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-1','2006-6-30','-32203');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-7-1','2006-12-31','-32203');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-3','2005-12-31','-32318');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-3','2006-2-28','-32318');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-3-1','2006-3-31','-32318');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-3-1','2006-12-31','-32318');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-7','2005-12-31','-32396');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-7','2006-3-31','-32396');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-7','2006-6-30','-32396');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-7-1','2006-11-15','-32396');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-13','2005-12-31','-32501');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-1-1','2006-3-31','-32501');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-1-1','2006-12-31','-32501');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-03-01','2005-06-30','-32396');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-08-24','2006-11-24','-32396');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-11-11','2007-02-25','-32396');
http://topic.csdn.net/u/20090923/13/baaa34b4-184d-4805-b6d4-747ec0845fe6.html?10721
--但我觉得这个题目非常值得大家一做,大家有什么更好的方法,欢迎建议!谢谢!
--现在有这样的一个问题
--有这样的一个表hist 表里有四个字段
-- 一个是id (行号id),一个是effdat(登记开始的时间),一个是expdat(登记结束时间),最后一个是userid(用户ID)
--一个人可能有多次登记 ,而且登记的时间可能是包含的关系。也可能是不连续的。
--现在就是判断都有那些人在一个特定的时间段内登记是连续的。
--比如说从‘2005-01-01’到‘2006-01-01’ 下面是一段查询的记录 select * from hist
SQL code
1 2005-6-14 2005-8-31 -32169
2 2005-7-1 2005-8-31 -32169
3 2005-9-1 2005-12-31 -32169
4 2006-1-1 2006-3-31 -32169
5 2006-1-1 2006-8-31 -32169
6 2006-9-1 2006-9-30 -32169
7 2006-10-1 2006-12-31 -32169
8 2005-6-21 2005-6-30 -32203
9 2005-7-1 2005-12-31 -32203
10 2005-7-1 2006-3-31 -32203
11 2005-7-1 2006-6-30 -32203
12 2006-7-1 2006-12-31 -32203
13 2005-7-3 2005-12-31 -32318
14 2005-7-3 2006-2-28 -32318
15 2006-3-1 2006-3-31 -32318
16 2006-3-1 2006-12-31 -32318
17 2005-7-7 2005-12-31 -32396
18 2005-7-7 2006-3-31 -32396
19 2005-7-7 2006-6-30 -32396
20 2006-7-1 2006-11-15 -32396
21 2005-7-13 2005-12-31 -32501
22 2006-1-1 2006-3-31 -32501
23 2006-1-1 2006-12-31 -32501
--大家看看这个查询的结果 我解释一下 第一个1 那是行号
--后面的2005-6-14 是登记的开始时间,2005-8-31是登记的结束时间,意思就是他在2005-6-14 到 2005-8-31 是连续登记的。
--后面的-32169 是人的id。
--但是第二条记录的开始时间和登记的结束时间分别是 2005-7-1 2005-8-31 这个包含在第一个时间里面,说明他在2005-6-14 2005-8-31是连续的。
--现在我就想求各位帮我写一个方法。无论是通过什么方法。可以是程序(限pb程序)能够求出这个表内在某个时间段内哪些人是连续登记的……
求求各位了……………………首先谢谢各位!!
-----------------------------------------------------------------------------------------------------------------------------
我们得先明白:我们到底怎么选择时间区间? ----比如说:我们要选择在 2006-01-01(起始时间段) 到 2006-11-12(结束时间段)这段时间内的连续用户
----是不是表里面的每条记录应该满足:effdat <='2006-11-12' and expdat>='2006-01-01' --------------------请仔细想想!
----每行记录的effdat应该小于等于 2006-11-12(结束时间段),并且:expdat应该大于等于2006-01-01(起始时间段),
----各位:好好想想:我说的对吗? 比如说:用户-32396在表中共有以下四条记录,
1 2005-7-7 2005-12-31 -32396
2 2005-11-7 2006-3-31 -32396
3 2006-4-7 2006-6-30 -32396
4 2006-6-1 2006-11-15 -32396
我要求解:用户在2006-01-01到2006-11-12日,是否是连续的,我们应该只考虑2、3、4三条记录就够了(正如我上面所说的) 3. 用户Id不是序列Id,是后面的-32169.....才是真正的用户ID,楼主:对吧?
---当然:这一点好多人没注意,迷惑了! 4. 整了几天:各位还没有把连续的概念给整明白:
比如说:下面四条记录:
1 2005-7-7 2005-12-31 -32396
2 2005-11-7 2006-3-31 -32396
3 2006-4-7 2006-6-30 -32396
4 2006-6-1 2006-11-15 -32396
第一条记录:无可否认:表示用户从2005-7-7 到 2005-12-31 是连续的, 当在判断第二记录(前提:userid得与前一条记录的userid相等)时,
我们先把第一记录的effdat看作oldeffdat,expdat看作oldexpdat(可以理解吧) 如果它后面存在任意一条记录,这条的effdat看作neweffdat,expdat看作newexpdat(可以理解吧) 此时:记录存在三种情况:
1: neweffdat 介于(可以等于)先前一条记录的effdat(oldeffdat) 与 expdat(oldffdat) 之间,
并且,newexpdat > oldexpdat
那么这个用户在时间区间 oldeffdat与newexpdat之间是连续的 2:newexpdat介于(可以等于)先前一条记录的effdat(oldeffdat) 与 expdat(oldffdat) 之间,
并且,neweffdat < oldeffdat
那么这个用户在时间区间 neweffdat与oldexpdat之间是连续的 3:neweffdat <= 先前一条记录的effdat(oldeffdat),并且 newexpdat >= 先前一条记录的expdat(oldeffdat)
也就是说先前一条记录的时间区间真包含于新记录的时间区间,
此时:那么说明这个用户在时间区间:neweffdat与newexpdat之间是连续的。 --同样的判断用于第一条记录后面的任意记录,依此类推! 不知道我说的,大家能不能明白,也不知道楼主是不是这个意思?
---------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[sequen_time] @startdate DATETIME, @enddate DATETIME
AS
/*
---时间段连续问题(SQL Server终结、优化版)
SELECT * FROM hist WHERE effdat<='20070101' and expdat>='20050707' order by effdat;
EXEC sequen_time '2005-07-01','2006-01-24'
*/
BEGIN
--select convert(datetime,'20061125',121) --如果输入(起始时间断)参数为空,则判断近半个月以来的连续用户
IF (ISNULL(@startdate,'')='')
SET @startdate=CONVERT(CHAR(10),GETDATE()-15,121)
IF (ISNULL(@enddate,'')='')
SET @enddate=CONVERT(CHAR(10),GETDATE(),121) DECLARE @count1 INT --用以查看是否有符合给定日期条件的记录
DECLARE @count2 INT --用以查看是否有符合给定日期条件的记录输出
DECLARE @count3 INT --用以查看是否有符合给定日期条件的记录输出 SELECT @count1=COUNT(Id) FROM hist
WHERE effdat<=@enddate AND expdat>=@startdate IF(@count1>0) --如果:有符合给定日期条件的记录
BEGIN CREATE TABLE #DBTempA(
Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userID VARCHAR(20) ) INSERT INTO #DBTempA(effdat,expdat,userID)
SELECT effdat,expdat,userID FROM hist
WHERE effdat<=@enddate AND expdat>=@startdate --用以保存符合给定时间段要求的用户记录
CREATE TABLE #DBTempB(
Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userID VARCHAR(20) ) --如果存在直接记录行符合给定日期条件:
INSERT INTO #DBTempB(effdat, expdat, userID)
SELECT effdat,expdat,userID FROM #DBTempA
WHERE effdat<=CONVERT(CHAR(10),@startdate,121) AND expdat>=CONVERT(CHAR(10),@enddate,121); --删除已经存在直接记录符合指定时间段的用户
DELETE FROM #DBTempA WHERE userID IN (SELECT userID FROM #DBTempB); --记录将要判断的记录行数,用以每次循环后递减,当@count为0时,退出While循环
SELECT @count2=COUNT(Id) FROM #DBTempA; WHILE(@count2>0) --如果还有记录:游标判断间接符合条件的记录
BEGIN DECLARE @userID INT --保存用户名,在游标中将根据用户名选择记录进行循环判断
DECLARE @oldID INT --记下ID,用以游标完成一次循环后删除记录
DECLARE @newID INT --记下ID,用以游标完成一次循环后删除记录
DECLARE @oldStartdate DATETIME
DECLARE @oldEnddate DATETIME
DECLARE @newStartdate DATETIME
DECLARE @newEnddate DATETIME SELECT TOP 1 @oldId=Id, @oldStartdate=effdat, @oldEnddate=expdat, @userID=userId FROM #DBTempA DECLARE @MyData CURSOR
SET @MyData = CURSOR FOR
SELECT effdat, expdat FROM #DBTempA WHERE userId=LTRIM(RTRIM(@userID)) AND Id<>@oldId
OPEN @MyData
FETCH next FROM @MyData INTO @newStartdate,@newEnddate
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@newStartdate>=@oldStartdate AND @newStartdate<=@oldEnddate AND @newEnddate>@oldEnddate)
BEGIN
SET @oldEnddate=@newEnddate;
END
IF(@newEnddate<=@oldEnddate AND @newEnddate>=@oldStartdate AND @newStartdate<@oldStartdate)
BEGIN
SET @oldStartdate=@newStartdate;
END
IF(@newStartdate<@oldStartdate AND @newEnddate>@oldEnddate )
BEGIN
SET @oldStartdate=@newStartdate;
SET @oldEnddate=@newEnddate;
END
FETCH next FROM @MyData INTO @newStartdate, @newEnddate
END
CLOSE @MyData
DEALLOCATE @MyData --如果找到符合条件的记录
IF (@oldStartdate<=@startdate AND @oldEnddate>=@enddate)
BEGIN
INSERT INTO #DBTempB(effdat, expdat, userID) SELECT @oldStartdate,@oldEnddate,@userId;
DELETE FROM #DBTempA WHERE userID=@userID;
SELECT @count2=COUNT(Id) FROM #DBTempA; --重置记数变量
END
ELSE
BEGIN
DELETE FROM #DBTempA WHERE userID=@userID;
SELECT @count2=COUNT(Id) FROM #DBTempA; --重置记数变量
END
END SELECT @count3=COUNT(ID) FROM #DBTempB;
IF(@count3>0)
SELECT * FROM #DBTempB;
ELSE
SELECT '对不起:没有符合要求的给定时间段内连续的记录!' '无符符合要求记录';
DROP TABLE #DBTempA,#DBTempB;
END ELSE
SELECT '对不起:没有您要的给定时间段内连续的记录!' '无符合初始条件记录';END
GOSET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
-------------------------------------------------------------------------------------------------建表,插入数据
CREATE TABLE hist(Id INT IDENTITY(1,1),
effdat DATETIME,
expdat DATETIME,
userId VARCHAR(10) )
INSERT INTO hist(effdat,expdat,userID)
VALUES('2005-6-14','2005-8-31','-32169');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-1','2005-8-31','-32169');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-9-1','2005-12-31','-32169');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-1-1','2006-3-31','-32169');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-1-1','2006-8-31','-32169');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-9-1','2006-9-30','-32169');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-10-1','2006-12-31','-32169');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-6-21','2005-6-30','-32203');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-1','2005-12-31','-32203');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-1','2006-3-31','-32203');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-1','2006-6-30','-32203');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-7-1','2006-12-31','-32203');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-3','2005-12-31','-32318');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-3','2006-2-28','-32318');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-3-1','2006-3-31','-32318');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-3-1','2006-12-31','-32318');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-7','2005-12-31','-32396');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-7','2006-3-31','-32396');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-7','2006-6-30','-32396');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-7-1','2006-11-15','-32396');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-7-13','2005-12-31','-32501');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-1-1','2006-3-31','-32501');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-1-1','2006-12-31','-32501');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2005-03-01','2005-06-30','-32396');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-08-24','2006-11-24','-32396');
INSERT INTO hist(effdat,expdat,userId)
VALUES('2006-11-11','2007-02-25','-32396');
effdat DATETIME,
expdat DATETIME,
userId VARCHAR(10) )INSERT INTO hist(effdat,expdat,userID) VALUES('2005-6-14','2005-8-31','-32169');
INSERT INTO hist(effdat,expdat,userId) VALUES('2005-7-1','2005-8-31','-32169');
INSERT INTO hist(effdat,expdat,userId) VALUES('2005-9-1','2005-12-31','-32169');
INSERT INTO hist(effdat,expdat,userId) VALUES('2006-1-1','2006-3-31','-32169');
INSERT INTO hist(effdat,expdat,userId) VALUES('2006-1-1','2006-8-31','-32169');
INSERT INTO hist(effdat,expdat,userId) VALUES('2006-9-1','2006-9-30','-32169');
INSERT INTO hist(effdat,expdat,userId) VALUES('2006-10-1','2006-12-31','-32169');
INSERT INTO hist(effdat,expdat,userId) VALUES('2005-6-21','2005-6-30','-32203');
INSERT INTO hist(effdat,expdat,userId) VALUES('2005-7-1','2005-12-31','-32203');
INSERT INTO hist(effdat,expdat,userId) VALUES('2005-7-1','2006-3-31','-32203');
INSERT INTO hist(effdat,expdat,userId) VALUES('2005-7-1','2006-6-30','-32203');
INSERT INTO hist(effdat,expdat,userId) VALUES('2006-7-1','2006-12-31','-32203');
INSERT INTO hist(effdat,expdat,userId) VALUES('2005-7-3','2005-12-31','-32318');
INSERT INTO hist(effdat,expdat,userId) VALUES('2005-7-3','2006-2-28','-32318');
INSERT INTO hist(effdat,expdat,userId) VALUES('2006-3-1','2006-3-31','-32318');
INSERT INTO hist(effdat,expdat,userId) VALUES('2006-3-1','2006-12-31','-32318');
INSERT INTO hist(effdat,expdat,userId) VALUES('2005-7-7','2005-12-31','-32396');
INSERT INTO hist(effdat,expdat,userId) VALUES('2005-7-7','2006-3-31','-32396');
INSERT INTO hist(effdat,expdat,userId) VALUES('2005-7-7','2006-6-30','-32396');
INSERT INTO hist(effdat,expdat,userId) VALUES('2006-7-1','2006-11-15','-32396');
INSERT INTO hist(effdat,expdat,userId) VALUES('2005-7-13','2005-12-31','-32501');
INSERT INTO hist(effdat,expdat,userId) VALUES('2006-1-1','2006-3-31','-32501');
INSERT INTO hist(effdat,expdat,userId) VALUES('2006-1-1','2006-12-31','-32501');
INSERT INTO hist(effdat,expdat,userId) VALUES('2005-03-01','2005-06-30','-32396');
INSERT INTO hist(effdat,expdat,userId) VALUES('2006-08-24','2006-11-24','-32396');
INSERT INTO hist(effdat,expdat,userId) VALUES('2006-11-11','2007-02-25','-32396');
--将多个存在交集或者相连的日期区间合并成一个大的时间区间
select
a.userID,a.effdat,min(b.expdat) as expdat
from
(select
userID,effdat
from
hist t
where
not exists(select
1
from
hist
where
userID=t.userID and t.effdat>effdat and t.effdat<=dateadd(dd,1,expdat))) a
inner join
(select
userID,expdat
from
hist t
where
not exists(select
1
from
hist
where
userID=t.userID and dateadd(dd,1,t.expdat)>=effdat and t.expdat<expdat)) b
on
a.userID=b.userID and a.effdat<=b.expdat
group by
a.userID,a.effdat
order by
a.userID,a.effdat
/*
userID effdat expdat
---------- ------------------------------------------------------ ------------------------------------------------------
-32169 2005-06-14 00:00:00.000 2006-12-31 00:00:00.000
-32203 2005-06-21 00:00:00.000 2006-12-31 00:00:00.000
-32318 2005-07-03 00:00:00.000 2006-12-31 00:00:00.000
-32396 2005-03-01 00:00:00.000 2005-06-30 00:00:00.000
-32396 2005-07-07 00:00:00.000 2007-02-25 00:00:00.000
-32501 2005-07-13 00:00:00.000 2006-12-31 00:00:00.000
*/
drop table hist
go
在CSDN很少有人叫偶潇湘,Dev的故人?
select
a.userID,a.effdat,min(b.expdat) as expdat from
(select
userID,effdat from
hist t where not exists(select 1 from
hist where
userID=t.userID and t.effdat>effdat and t.effdat <=dateadd(dd,1,expdat))) a inner join
(select
userID,expdat from
hist t where not exists(select 1 from
hist where
userID=t.userID and dateadd(dd,1,t.expdat)>=effdat and t.expdat <expdat)) b on
a.userID=b.userID and a.effdat <=b.expdat group by
a.userID,a.effdat order by
a.userID,a.effdat
---------------------------------------------------
-32169 2005-06-14 00:00:00.000 2006-12-31 00:00:00.000
-32203 2005-06-21 00:00:00.000 2006-12-31 00:00:00.000
-32318 2005-07-03 00:00:00.000 2006-12-31 00:00:00.000
-32396 2005-03-01 00:00:00.000 2005-06-30 00:00:00.000
-32396 2005-07-07 00:00:00.000 2007-02-25 00:00:00.000
-32501 2005-07-13 00:00:00.000 2006-12-31 00:00:00.000
--------------------------------------------------------
--你看一下用户-32396,在2006-07-01是不是断了?
select * from hist where userid='-32396';
---------------------------------------------------------
47 2005-07-07 00:00:00.000 2005-12-31 00:00:00.000 -32396
48 2005-07-07 00:00:00.000 2006-03-31 00:00:00.000 -32396
49 2005-07-07 00:00:00.000 2006-06-30 00:00:00.000 -32396
50 2006-07-01 00:00:00.000 2006-11-15 00:00:00.000 -32396
54 2005-03-01 00:00:00.000 2005-06-30 00:00:00.000 -32396
55 2006-08-24 00:00:00.000 2006-11-24 00:00:00.000 -32396
56 2006-11-11 00:00:00.000 2007-02-25 00:00:00.000 -32396
----------------------------------------------------------
Id=50的时候,是不是断了?
--当然:只要SQL 语句能写出来,这也很容易了!