如何在Server中按照以下方法对数据进行分组？差距和岛屿
如何在Server中按照以下方法对数据进行分组？差距和岛屿
提问于 2017-11-06 10:39:32
DECLARE @Table TABLE
(
Col_status VARCHAR(5),
Col_Location VARCHAR(5),
Col_NUM INT
)
INSERT INTO @Table (Col_status, Col_Location, Col_NUM)
VALUES ('ON', 'AAA', 1), ('ON', 'AAA', 2),
('OFF', 'AAA', 3), ('ON', 'BBB', 4),
('ON', 'BBB', 5), ('ON', 'CCC', 6), ('ON', 'BBB', 7),
('OFF', 'AAA', 9), ('OFF', 'AAA', 10);
SELECT *
FROM @Table输出：
Col_status Col_Location Col_NUM
---------- ------------ -------
ON AAA 1
ON AAA 2
OFF AAA 3
ON BBB 4
ON BBB 5
ON CCC 6
ON BBB 7
OFF AAA 9
OFF AAA 10但我希望得到以下答案:我大部分时间都花在了这个问题上。
- 按第一列和第二列分组，但应根据第三列值的顺序进行拆分。
产出应如下：
Col_status Col_Location Min(Col_NUM) count(Col_NUM)
---------- ------------- ------------- --------------
ON AAA 1 2
OFF AAA 3 1
ON BBB 4 2
ON CCC 6 1
ON BBB 7 1
OFF AAA 9 2谢谢和问候，Kathir M
回答 5
Stack Overflow用户
回答已采纳
发布于 2017-11-06 10:50:31
这是一个孤岛和缺口问题。它可以这样解决：
select t.Col_status,t.Col_Location, min(t.col_num), count(*)
from
(
select t.*, row_number() over (partition by Col_status,Col_Location order by Col_NUM) group_rn
from @Table t
) t
group by t.Col_status,t.Col_Location, t.col_num - t.group_rn演示
基本思想是在t.col_num - t.group_rn中，其中相同序列中的行(具有连续的id)有相同的结果。因此，t.col_num - t.group_rn允许您隔离这些序列并正确地进行分组。
Stack Overflow用户
发布于 2017-11-06 10:56:05
试试这个：
DECLARE @Table TABLE (
Col_status varchar(5),
Col_Location varchar(5),
Col_NUM INT
)
INSERT INTO @Table (Col_status,Col_Location,Col_NUM)
VALUES ('ON','AAA',1),('ON','AAA',2),('OFF','AAA',3),('ON','BBB',4),
('ON','BBB',5),('ON','CCC',6),('ON','BBB',7),('OFF','AAA',9),('OFF','AAA',10);
select A.Col_status,A.Col_Location
,MIN(A.col_num)[Min(Col_NUM)]
,COUNT(1)[count(Col_NUM)]
FROM
(
SELECT t.*
,ROW_NUMBER() OVER (PARTITION BY Col_status,Col_Location ORDER BY Col_NUM) RN
FROM @Table t
) A
group by A.Col_status,A.Col_Location, A.col_num - A.RN
ORDER BY min(A.col_num)Stack Overflow用户
发布于 2017-11-06 11:11:04
正如先前的答复所提到的那样，这是一个空白和岛屿问题。
因此，您需要创建一个将生成岛屿的查询，因此下面是一个示例
;WITH cteX
AS(
SELECT TOP 100 PERCENT
t.Col_NUM
, t.Col_Location
, t.Col_status
, RN = ROW_NUMBER() OVER (PARTITION BY Col_status,Col_Location ORDER BY Col_NUM)
FROM @Table t
ORDER BY t.Col_NUM
)
SELECT
X.Col_NUM
, X.Col_Location
, X.Col_status
, X.RN
, Island = X.Col_NUM - X.RN
FROM
cteX X
ORDER BY
X.Col_NUM - X.RN输出显示群岛
因此，使用此查询，您可以获得所需的结果。
;WITH cteX
AS(
SELECT TOP 100 PERCENT
t.Col_NUM
, t.Col_Location
, t.Col_status
, RN = ROW_NUMBER() OVER (PARTITION BY Col_status,Col_Location ORDER BY Col_NUM)
FROM @Table t
ORDER BY t.Col_NUM
)
SELECT
X.Col_status
, X.Col_Location
, MIN_Col_Num = MIN(X.Col_NUM)
, COUNT_Col_NUM = COUNT(*)
FROM cteX X
GROUP BY
X.Col_Location
, X.Col_status
, (X.Col_NUM - X.RN)
ORDER BY
MIN_Col_Num,,(X.Col_NUM - X.RN)输出
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47134969
复制相关文章
相似问题
腾讯云开发者
