求连续出现的值和次数

[ftfox_2000]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
id int,
[name] NVARCHAR(10),

)
GO
SET NOCOUNT ON
INSERT INTO t VALUES (1,'A')
INSERT INTO t VALUES (2,'A')
INSERT INTO t VALUES (3,'A')
INSERT INTO t VALUES (6,'B')
INSERT INTO t VALUES (7,'A')
INSERT INTO t VALUES (8,'A')

要结果 name counts column1 coloum2
A 2 3 2
B 1 2

[Dear SQL(燊)]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
    id int,
    [name] NVARCHAR(10),
    
)
GO
SET NOCOUNT ON
INSERT INTO t VALUES (1,'A')
INSERT INTO t VALUES (2,'A')
INSERT INTO t VALUES (3,'A')
INSERT INTO t VALUES (6,'B')
INSERT INTO t VALUES (7,'A')
INSERT INTO t VALUES (8,'A')

;
with list as(
	select *,rid=row_number ( ) OVER (order by id),lead_name=lead(name)over(order by id)
	from t
)
select [name]
	,counts=count(*)over(partition by name)
	,[column]=rid-isnull(lag(rid)over(order by id),0)
from list
where name<>isnull(lead_name,'')

name       counts      column
---------- ----------- --------------------
A          2           3
B          1           1
A          2           2

行转参考：https://bbs.csdn.net/topics/392504654

[Dear SQL(燊)]

如是sql server 不是 2012+，用left join on rid=rid-1得到lead值

[砸死牛顿的苹果]

好像是彩票一类的问题

[Dear SQL(燊)]

引用 3 楼 砸死牛顿的苹果 的回复:

好像是彩票一类的问题

是哦，我天天买彩票都没有想起

[ftfox_2000]

分给错人了。。。。。。