如何在sql中按顺序排列日期范围 [英] How to arrange the date ranges in sequence in sql
问题描述
这里我需要按顺序排列日期范围usr_id。
CREATE TABLE #temp(st_date DATE,end_date DATE,usr_id INT)
INSERT #temp
VALUES('2007-03-01','2015-01-31',1),
('2015-02- 01','2015-05-05',1),
('2015-05-06','2017-04-01',1),
('2007-03-01','2014-01-31',2),
('2007-03-01','2015-01-31',3),
('2015-02-01','2017-04-01',3),
('2020-02-01','2020-06-05' ,1),
('2020-06-06','2020-08-08',1)
SELECT t2.st_date,t1.end_date,t1。 usr_id FROM #temp t1,#temp t2
WHERE t1.st_date = dateadd(dd,+ 1,t2.end_date)AND t2.usr_id = t1.usr_id
DROP TABLE #temp
我的答案应该是这样的。
st_date end_date usr_id
2007-03-01 2017-04-01 1
2020-02-01 2020-08-08 1
2007-03-01 2017-04-01 3
2007-03-01 2014-01-31 2
Here I need to arrange the date ranges in sequence by usr_id .
CREATE TABLE #temp(st_date DATE,end_date DATE,usr_id INT)
INSERT #temp
VALUES ('2007-03-01 ','2015-01-31 ',1),
('2015-02-01 ','2015-05-05 ',1),
('2015-05-06 ','2017-04-01 ',1),
('2007-03-01 ','2014-01-31 ',2),
('2007-03-01 ','2015-01-31 ',3),
('2015-02-01 ','2017-04-01 ',3),
('2020-02-01 ','2020-06-05 ',1),
('2020-06-06 ','2020-08-08 ',1)
SELECT t2.st_date,t1.end_date,t1.usr_id FROM #temp t1 ,#temp t2
WHERE t1.st_date = dateadd(dd,+1,t2.end_date) AND t2.usr_id = t1.usr_id
DROP TABLE #temp
My answer should be like this .
st_date end_date usr_id
2007-03-01 2017-04-01 1
2020-02-01 2020-08-08 1
2007-03-01 2017-04-01 3
2007-03-01 2014-01-31 2
推荐答案
因为看起来你可以使用T-SQL块,我想一个简单的方法就是使用暴力方法。请考虑以下代码:
Since it seems you can use T-SQL blocks I guess a simple way to do this is a brute force approach. Consider the following code:
-- New temp table
CREATE TABLE #temp2(st_date DATE,end_date DATE,usr_id INT);
--Initial rows
INSERT INTO #temp2 (st_date, end_date, usr_id)
SELECT st_date, end_date, usr_id
FROM #temp t
WHERE NOT EXISTS (SELECT 1
FROM #temp t2
WHERE t2.end_date = DATEADD(day, -1, t.st_date));
-- Update the end date until nothing to update anymore
BEGIN
WHILE 1=1 BEGIN
UPDATE t2
SET end_date = t.end_date
FROM #temp2 t2
INNER JOIN #temp t
ON t2.end_date = DATEADD(day, -1, t.st_date);
IF @@ROWCOUNT = 0 BEGIN
BREAK;
END;
END;
END;
-- Select the data
SELECT * FROM #temp2 ORDER BY usr_id, st_date;
这篇关于如何在sql中按顺序排列日期范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!