SQL Server GROUP BY COUNT 仅连续行 [英] SQL Server GROUP BY COUNT Consecutive Rows Only

问题描述

我在 Microsoft SQL Server 2008 R2 上有一个名为 DATA 的表，其中包含三个不可为空的整数字段:ID、序列和值.具有相同 ID 的序列值将是连续的，但可以以任何值开头.我需要一个查询，该查询将返回具有相同 ID 和值的连续行的计数.

I have a table called DATA on Microsoft SQL Server 2008 R2 with three non-nullable integer fields: ID, Sequence, and Value. Sequence values with the same ID will be consecutive, but can start with any value. I need a query that will return a count of consecutive rows with the same ID and Value.

例如，假设我有以下数据:

For example, let's say I have the following data:

ID  Sequence  Value
--  --------  -----
1         1      1
5         1    100
5         2    200
5         3    200
5         4    100
10       10     10

我想要以下结果:

ID  Start  Value  Count
--  -----  -----  -----
1      1      1      1
5      1    100      1
5      2    200      2
5      4    100      1
10    10     10      1

我试过了

SELECT ID, MIN([Sequence]) AS Start, Value, COUNT(*) AS [Count]
 FROM DATA
 GROUP BY ID, Value
 ORDER BY ID, Start

但这给了

ID  Start  Value  Count
--  -----  -----  -----
1      1      1      1
5      1    100      2
5      2    200      2
10    10     10      1

将具有相同值的所有行分组，而不仅仅是连续的行.

which groups all rows with the same values, not just consecutive rows.

有什么想法吗?从我所看到的，我相信我必须使用 ROW_NUMBER() 在连续行上将表与自身连接起来，但我不确定如何从中获取计数.

Any ideas? From what I've seen, I believe I have to left join the table with itself on consecutive rows using ROW_NUMBER(), but I am not sure exactly how to get counts from that.

提前致谢.

推荐答案

您可以使用 Sequence - ROW_NUMBER() OVER (ORDER BY ID, Val, Sequence) AS g 创建组:

You can use Sequence - ROW_NUMBER() OVER (ORDER BY ID, Val, Sequence) AS g to create a group:

SELECT
  ID,
  MIN(Sequence) AS Sequence,
  Val,
  COUNT(*) AS cnt
FROM
(
  SELECT
    ID,
    Sequence,
    Sequence - ROW_NUMBER() OVER (ORDER BY ID, Val, Sequence) AS g,
    Val
  FROM
    yourtable
) AS s
GROUP BY
  ID, Val, g

请在此处查看小提琴.

这篇关于SQL Server GROUP BY COUNT 仅连续行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！