27,579
社区成员
发帖
与我相关
我的任务
分享
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