mssql sqlserver 查询表数据,相邻数据行只保留一行


摘要:
下文通过脚本的方式实现,获取表数据,如果相邻行数据中值相同,则返回一条记录
实验环境:sqlserver 2008 R2


IF OBJECT_ID('test') IS NOT NULL DROP TABLE test
GO
CREATE TABLE test(
	info NVARCHAR(30)	
)
GO
INSERT INTO test(info) VALUES (1),(8),(2),(5),(5),(8),(8)
 
;WITH tmp AS (
	SELECT ROW_NUMBER() OVER ( ORDER BY (select 1) ) AS keyId, * FROM test
)
SELECT a.keyId,a.info FROM tmp AS a 
WHERE NOT EXISTS (
	SELECT * FROM tmp AS b WHERE a.keyId=b.keyId-1 AND a.info=b.info	
)

go
truncate table test 
drop     table test

—————输出—————
keyId info
1 1
2 8
3 2
5 5
7 8

mssql_sqlserver_保留一条数据

mssql_sqlserver_保留一条数据