我在存盤地毯的 SQL Server 中有一個表。每個都有自己的 ID，以及一個帶有顏色的范圍（以米為單位）。
CREATE TABLE rugs (
[code] VARCHAR (10),
[from] INT,
[to] INT,
[color] VARCHAR (10)
);
INSERT INTO rugs VALUES ('RUG001', 0, 1, 'Yellow');
INSERT INTO rugs VALUES ('RUG001', 1, 2, 'Red');
INSERT INTO rugs VALUES ('RUG001', 2, 4, 'Blue');
INSERT INTO rugs VALUES ('RUG001', 3, 5, 'Green');
INSERT INTO rugs VALUES ('RUG002', 0, 1, 'Purple');
INSERT INTO rugs VALUES ('RUG002', 1, 2, 'Orange');
在此示例中，它在與地毯 RUG001 的藍色和綠色對應的行中重疊。RUG002 沒問題。
對于同一張地毯，我如何檢查是否有重疊的間隔？
我試過的（它不起作用）
Select * from RUGS as R1
INNER JOIN RUGS as R2
ON R1.CODE = R2.CODE
WHERE
R1.[FROM] <= R2.[TO] AND R1.[TO] >= R2.[FROM]
uj5u.com熱心網友回復：
使用小資料集很難確定，但以下對您有用嗎？使用超前/滯后檢查下一個或最后一個范圍是否在當前范圍內。
with rug as (
select * ,
Lead([from]) over(partition by code order by [from]) nextfrom,
Lag([to]) over(partition by code order by [to]) lastto
from rugs
)
select code, [from], [to], color
from rug
where nextfrom<[to] or lastto>[from]
uj5u.com熱心網友回復：
這里有兩種可能的解決方案。
- 就在左側重疊：
SELECT * FROM RUGS R1
INNER JOIN RUGS R2 ON R1.CODE = R2.CODE
WHERE R1.[TO] < R2.[TO] AND R2.[FROM] < R1.[TO]
--Result
/*
code from to color code from to color
RUG001 2 4 Blue RUG001 3 5 Green
*/
- 所有重疊：
SELECT * FROM RUGS R1
INNER JOIN RUGS R2 ON R1.CODE = R2.CODE
WHERE R1.[FROM] < R2.[FROM] AND R2.[TO] < R1.[TO]
OR (R2.[FROM] < R1.[FROM] AND R2.[TO] < R1.[TO] AND R1.[FROM] < R2.[TO])
OR (R1.[FROM] < R2.[FROM] AND R1.[TO] < R2.[TO] AND R2.[FROM] < R1.[TO])
-- Result
/*
code from to color code from to color
RUG001 2 4 Blue RUG001 3 5 Green
RUG001 3 5 Green RUG001 2 4 Blue
*/
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/374178.html
標籤：sql sql-server 查询语句
上一篇：對SQLServer中的字串中的每個時間重復一周中的某一天
下一篇：生成范圍內的亂數字、字母或字符