我有一个表的结构如下，我要以第一行内容做条件，得到跟第一行内容连续重复的次数。
备注：ID是连续的主键
/****** Script for SelectTopNRows command from SSMS ******/
SELECT [id]
,[column1]
,[column2]
,[column3]
FROM [dbo].[tb]
ORDER BY id DESC
id column1 column2 column3
----------- ----------------------- -------------- -------
6 ccc mm ttt <------以第一行内容做条件
5 ccc mm ttt
4 ccc mm ddd
3 ccc bbb ddd
2 aaa bbb ddd
1 aaa mm ccc
--------我想得到的结果
列名 |内容 |次数
---------------------
column1 | CCC | 4
column2 | mm | 3
column3 | ttt | 2
----测试脚本
CREATE TABLE [dbo].[tb](
[id] [int] IDENTITY(1,1) NOT NULL,
[column1] [nvarchar](50) NULL,
[column2] [nvarchar](50) NULL,
[column3] [nvarchar](50) NULL,
CONSTRAINT [PK_tb] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tb] ON
INSERT [dbo].[tb] ([id], [column1], [column2], [column3]) VALUES (1, N'aaa', N'mm', N'ccc')
INSERT [dbo].[tb] ([id], [column1], [column2], [column3]) VALUES (2, N'aaa', N'bbb', N'ddd')
INSERT [dbo].[tb] ([id], [column1], [column2], [column3]) VALUES (3, N'ccc', N'bbb', N'ddd')
INSERT [dbo].[tb] ([id], [column1], [column2], [column3]) VALUES (4, N'ccc', N'mm', N'ddd')
INSERT [dbo].[tb] ([id], [column1], [column2], [column3]) VALUES (5, N'ccc', N'mm', N'ttt')
INSERT [dbo].[tb] ([id], [column1], [column2], [column3]) VALUES (6, N'ccc', N'mm', N'ttt')
SET IDENTITY_INSERT [dbo].[tb] OFF
------解决方案--------------------
;WITH CTE AS
(
SELECT
id,column1,column2,column3,
rowno1 = id-ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY id),
rowno2 = id-ROW_NUMBER() OVER (PARTITION BY column2 ORDER BY id),
rowno3 = id-ROW_NUMBER() OVER (PARTITION BY column3 ORDER BY id)
FROM TB
),
cte2 as
(
SELECT TOP(1)
column1, column2, column3,
rowno1=COUNT(1) OVER(PARTITION BY rowno1),
rowno2=COUNT(1) OVER(PARTITION BY rowno2),