如何在SQL Server中按数据进行分组?差距和岛屿

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

Output:

输出:

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

But I expect the following answer; I spent most of my time on this.

但我希望得到以下答案;我把大部分时间都花在这上面了。

group by first and second columns, but it should be split based on sequence of third column value
由第一列和第二列组成的组，但应该根据第三列值的序列进行拆分。

Output should be like this:

输出应如下:

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

Thanks and regards, Kathir M

谢谢你，凯瑟琳。

5 个解决方案

#1

It is an island and gaps problem. It can be solved like this:

这是一个岛屿和差距的问题。可以这样解决:

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

demo

演示

the basic idea is in the t.col_num - t.group_rn, where the rows in the same sequence (having consecutive id) has the same result of this difference. Therefore, the t.col_num - t.group_rn allows you to isolate these sequences and do the group by correctly.

基本思想在t。col_num - t。group_rn，其中相同序列(具有连续id)中的行具有相同的差异结果。因此,t。col_num - t。group_rn允许您隔离这些序列并正确地执行组。

#2

As mention by a previous answer this is a gaps and islands question.

正如前面的回答所提到的，这是一个差距和岛屿问题。

So you need to create a query that will generate islands, so this example, here is one

你需要创建一个查询来生成岛屿，这个例子，这是一个

;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

Output shows the Islands

输出显示了这些岛屿

so using this query you can get the results you want

所以使用这个查询你可以得到你想要的结果

;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)

Output

输出

#3

Try this:

试试这个:

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)

#4

Could you not just select the result into a temp table and then do this:

你能不能将结果选择到一个临时表中，然后这样做:

SELECT Col_Location, 
       Col_Status, 
       MIN (Col_Num), 
       COUNT(*) AS Count(Col_NUM) 
FROM #TMP
GROUP BY Col_Location, 
         Col_Status
ORDER BY 1, 2

#5

In this case, you need to group by two columns :) Try this query:

在这种情况下，您需要按两列进行分组:)

SELECT Col_status, Col_Location, MIN(Col_NUM), COUNT(Col_NUM)
FROM @Table
GROUP BY Col_status, Col_Location

#1