多列重叠日期
;with cte as (
select Domain_Id, Starting_Date, End_Date
from Que_Date
union all
select t.Domain_Id, cte.Starting_Date, t.End_Date
from cte
join Que_Date t on cte.Domain_Id = t.Domain_Id and cte.End_Date = t.Starting_Date),
cte2 as (
select *, rn = row_number() over (partition by Domain_Id, End_Date order by Domain_Id)
from cte
)
select DISTINCT Domain_Id, Starting_Date, max(End_Date) enddate
from cte2
where rn=1
group by Domain_Id, Starting_Date
order by Domain_Id, Starting_Date;
select * from Que_Date
这是我写的代码，但是我得到了一个额外的行，即第二行是额外的，预期的输出应该只有第1行，第3行和第4行作为输出，所以请帮助我 .
我附上了一个显示输入，例外输出和我得到的输出的图像 .
回答(2)
2 years ago
你的第一个 cte 你有这么多的结果 . 您的第一个 cte 包含域名 . 因此，您无法根据 cte 过滤域名 . 所以你查询有不必要的行 .
尝试此解决方案 . Cte ConsistentDomains 只有一致的域名 . 因此，基于此cte，我们可以得到不重叠的结果 .
创建和填充数据：
CREATE TABLE FooTable
(
Domain_ID INT,
Starting_Date DATE,
End_Date Date
)
INSERT INTO dbo.FooTable
(
Domain_ID,
Starting_Date,
End_Date
)
VALUES
( 1, -- Domain_ID - int
CONVERT(datetime,'01-01-2011',103), -- Starting_Date - date
CONVERT(datetime,'05-01-2011',103) -- End_Date - date
)
, (1, CONVERT(datetime,'05-01-2011',103), CONVERT(datetime,'07-01-2011',103))
, (1, CONVERT(datetime,'07-01-2011',103), CONVERT(datetime,'15-01-2011',103))
, (2, CONVERT(datetime,'11-05-2011',103), CONVERT(datetime,'12-05-2011',103))
, (2, CONVERT(datetime,'13-05-2011',103), CONVERT(datetime,'14-05-2011',103))
查询以查找不重叠的结果：
DECLARE @startDate varchar(50) = '2011-01-01';
WITH ConsistentDomains AS
(
SELECT
f.Domain_ID
, f.Starting_Date
, f.End_Date
FROM FooTable f
WHERE f.Starting_Date = @startDate
UNION ALL
SELECT
s.Domain_ID
, s.Starting_Date
, s.End_Date
FROM FooTable s
INNER JOIN ConsistentDomains cd
ON s.Domain_ID = cd.Domain_ID
AND s.Starting_Date = cd.End_Date
), ConsistentDomainsRownumber AS
(
SELECT
cd.Domain_ID
, cd.Starting_Date
, cd.End_Date
, ROW_NUMBER() OVER (PARTITION BY cd.Domain_ID ORDER BY cd.Starting_Date,
cd.End_Date) RN
FROM ConsistentDomains cd
)
SELECT cd.Domain_ID
, convert(varchar, cd.Starting_Date, 105) Starting_Date
, convert(varchar, cd.End_Date, 105) End_Date
FROM ConsistentDomainsRownumber cd WHERE cd.RN = 1
UNION ALL
SELECT
ft.Domain_ID
, convert(varchar, ft.Starting_Date, 105) Starting_Date
, convert(varchar, ft.End_Date, 105) End_Date
FROM dbo.FooTable ft WHERE ft.Domain_ID NOT IN (SELECT cd.Domain_ID FROM
ConsistentDomainsRownumber cd)
输出：
2 years ago
我使用了@stepup提供的相同的表创建脚本，但您也可以通过这种方式获得结果 .
您可以使用自联接和Firs_value以及组中的最后一个值，以确保您在相同的ID和重叠日期内进行比较 .
输出：