从表中选择连续范围 [英] Select continuous ranges from table
本文介绍了从表中选择连续范围的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要基于连续数字(第N列)和与这些数字相关的相同类别"(下面的C列)从表中提取连续范围.图形上看起来像这样:
I need to extract continous ranges from a table based on consecutive numbers (column N) and same "category" these numbers relate to (column C below). Graphically it looks like this:
N C D
--------
1 x a C N1 N2 D1 D2
2 x b ------------------
3 x c x 1 4 a d (continuous range with same N)
4 x d ==> x 6 7 e f (new range because "5" is missing)
6 x e y 8 10 g h (new range because C changed to "y")
7 x f
8 y g
9 y h
10 y i
SQL Server是2005.谢谢.
SQL Server is 2005. Thanks.
推荐答案
DECLARE @myTable Table
(
N INT,
C CHAR(1),
D CHAR(1)
)
INSERT INTO @myTable(N,C,D) VALUES(1, 'x', 'a');
INSERT INTO @myTable(N,C,D) VALUES(2, 'x', 'b');
INSERT INTO @myTable(N,C,D) VALUES(3, 'x', 'c');
INSERT INTO @myTable(N,C,D) VALUES(4, 'x', 'd');
INSERT INTO @myTable(N,C,D) VALUES(6, 'x', 'e');
INSERT INTO @myTable(N,C,D) VALUES(7, 'x', 'f');
INSERT INTO @myTable(N,C,D) VALUES(8, 'y', 'g');
INSERT INTO @myTable(N,C,D) VALUES(9, 'y', 'h');
INSERT INTO @myTable(N,C,D) VALUES(10, 'y', 'i');
WITH StartingPoints AS(
SELECT A.*, ROW_NUMBER() OVER(ORDER BY A.N) AS rownum
FROM @myTable AS A
WHERE NOT EXISTS(
SELECT *
FROM @myTable B
WHERE B.C = A.C
AND B.N = A.N - 1
)
),
EndingPoints AS(
SELECT A.*, ROW_NUMBER() OVER(ORDER BY A.N) AS rownum
FROM @myTable AS A
WHERE NOT EXISTS (
SELECT *
FROM @myTable B
WHERE B.C = A.C
AND B.N = A.N + 1
)
)
SELECT StartingPoints.C,
StartingPoints.N AS [N1],
EndingPoints.N AS [N2],
StartingPoints.D AS [D1],
EndingPoints.D AS [D2]
FROM StartingPoints
JOIN EndingPoints ON StartingPoints.rownum = EndingPoints.rownum
结果:
C N1 N2 D1 D2
---- ----------- ----------- ---- ----
x 1 4 a d
x 6 7 e f
y 8 10 g i
这篇关于从表中选择连续范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文