已ID列为标识,当ID列数据有重复时候,每个ID类别,随机选,只1行数据.
=------------------------------------------------
希望得到这样的结果.
id col1 col2 col3
AAA 1 2 3
BBB 1 A 3
CCC A 2 3
注:要求每次返回的结果是一样的,不能这次返回第一条,下次返回第二条.
=------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))
DROP TABLE [dbo].[test]
GO
CREATE TABLE [dbo].[test](
[id] [varchar](50) NULL,
[col1] [varchar](50) NULL,
[col2] [varchar](50) NULL,
[col3] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT INTO test VALUES('AAA','1','2','3')
INSERT INTO test VALUES('AAA','1','2','3')
INSERT INTO test VALUES('BBB','1','A','3')
INSERT INTO test VALUES('BBB','1','2','B')
INSERT INTO test VALUES('CCC','A','2','3')
INSERT INTO test VALUES('CCC','1','B','3')
INSERT INTO test VALUES('CCC','1','2','C')
go
select * from test
------解决思路----------------------
select * from
(
select ROW_NUMBER()over(partition by id order by id) as rn,* from test
)t where rn=1