求个小算法，存储过程，附测试脚本,该怎么处理

求个小算法，，存储过程，，附测试脚本
本帖最后由 rd16 于 2013-09-16 02:36:47 编辑 我有一个表的结构如下，我要以第一行内容做条件，得到跟第一行内容连续重复的次数。

备注：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), 
 
rowno3=COUNT(1) OVER(PARTITION BY rowno3)
FROM CTE
ORDER BY id DESC
)
SELECT 列名='column1', 内容=column1, 次数=rowno1 FROM cte2
UNION ALL
SELECT 'column2', column2, rowno2 FROM cte2
UNION ALL
SELECT 'column3', column3, rowno3 FROM cte2
/*
列名内容次数
column1ccc4
column2mm3
column3ttt2
*/

[解决办法]

引用:

大牛，如果读取第一行数据后，再用游标往下读取不知能实现不？

用游标的话，你要遍历所有的结果集，还是用SQL效率比较好些。倒是可以用游标来动态读取列,下面SQL仅做参考：

--游标
IF object_id('tempdb..#temp','u') IS NOT NULL DROP TABLE #temp

CREATE TABLE #temp
(
列名 NVARCHAR(255),
内容 NVARCHAR(255),
次数 INT
)

DECLARE @columnlist NVARCHAR(max), @sql NVARCHAR(max), @columnname NVARCHAR(255), @cnt INT
SET @columnlist = N''
SELECT @columnlist = @columnlist + ',' + QUOTENAME(b.name)
FROM sys.tables a
INNER JOIN sys.columns b
ON a.object_id = b.object_id
WHERE a.name = 'tb'
AND b.name <> 'id'
SET @columnlist = STUFF(@columnlist,1,1,'')

SET @sql = N'
SELECT 列名, 内容 FROM
(SELECT TOP(1) * FROM dbo.tb ORDER BY id DESC) a
UNPIVOT
(内容 FOR 列名 IN('+ @columnlist +')) b
'

INSERT #temp(列名, 内容)
EXEC(@sql)

DECLARE cursor_name CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT 列名 FROM #temp

OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @columnname

WHILE (@@FETCH_STATUS = 0) 
BEGIN
SET @sql = N'
update #temp
set 次数 = 
(
SELECT COUNT(1) FROM
(
SELECT TOP(1) WITH TIES gp=id-ROW_NUMBER() OVER (PARTITION BY '+ QUOTENAME(@columnname) +' ORDER BY id)
FROM dbo.tb
ORDER BY gp DESC
) t
)
where 列名 = '''+ @columnname +'''
'

EXEC(@sql)

FETCH NEXT FROM cursor_name INTO @columnname
END

CLOSE cursor_name
DEALLOCATE cursor_name

SELECT * FROM #temp

查看更多 下一篇