选择连续出现多次的记录

Question

我的问题是我想选择一些出现在一行中的记录。例如，我们有这样的表格：

x
x
x
y
y
x
x
y

查询应该给出这样的答案：

x   3
y   2
x   2
y   1

Answer 1

SQL表表示无序集合。只有当有一列指定了排序时，您的问题才有意义。如果是这样，您可以使用行号的差异来确定组，然后进行聚合：

select col1, count(*)
from (select t.*,
row_number() over (order by <ordering col>) as seqnum,
row_number() over (partition by col1 order by <ordering col>) as seqnum_2
from t
) t
group by col1, (seqnum - seqnum_2)

Answer 2

我做了一个SQL Fiddlehttp://sqlfiddle.com/#！18/f8900/5

CREATE TABLE [dbo].[SomeTable](
[data] [nchar](1) NULL,
[id] [int] IDENTITY(1,1) NOT NULL
);
INSERT INTO SomeTable
([data])
VALUES
('x'),
('x'),
('x'),
('y'),
('y'),
('x'),
('x'),
('y')
;
select * from SomeTable;
WITH SomeTable_CTE (Data, total, BaseId, NextId)
AS
(
SELECT 
Data,
1 as total,
Id as BaseId,
Id+1 as NextId
FROM SomeTable
where not exists(
Select * from SomeTable Previous
where Previous.Id+1 = SomeTable.Id 
and Previous.Data = SomeTable.Data)
UNION ALL
select SomeTable_CTE.Data, SomeTable_CTE.total+1, SomeTable_CTE.BaseId as BaseId, SomeTable.Id+1 as NextId
from SomeTable_CTE inner join SomeTable on
SomeTable.Data = SomeTable_CTE.Data
and 
SomeTable.Id = SomeTable_CTE.NextId
)
SELECT Data, max(total)   as total
FROM SomeTable_CTE
group by Data, BaseId
order by BaseId

Answer 3

房间里的大象是用来建立行顺序的缺失列。

SELECT col1, count(*)
FROM  (
SELECT col1, order_column
, row_number() OVER (ORDER BY order_column)
- row_number() OVER (PARTITION BY col1 ORDER BY order_column) AS grp
FROM tbl
) t
GROUP  BY col1, grp
ORDER  BY min(order_column);

要排除只有单行的分区，请添加HAVING子句：

SELECT col1, count(*)
FROM  (
SELECT col1, order_column
, row_number() OVER (ORDER BY order_column)
- row_number() OVER (PARTITION BY col1 ORDER BY order_column) AS grp
FROM tbl
) t
GROUP  BY col1, grp
HAVING count(*) > 1
ORDER  BY min(order_column);

db<gt；小提琴这里

添加最后一个ORDER BY以保持原始顺序(以及有意义的结果(。您可能还需要添加一个类似min(order_column)的列。

相关：

• 找到每个玩家最长的完美得分记录
• 选择最长连续序列
• 按重复属性分组