把select 随机排序的结果保存在存储过程中的临时表中
通过查询语句,最后我要得到的是
得到的结果里面有两列。
就是对于name列有重复行的随机取其中的一行
比如name='b'的取id=2那一行在结果中,当然取id=5的那一行在结果中也可以。
这个问题想了好久,最后写了一个存储过程
CREATE proc Random
as
CREATE TABLE #a
(
ID [int] NOT NULL,
Name [varchar](50) NOT NULL,
)
insert into #a select * from table order by newid() asc
select * from #a t where
exists
(
select 1 from
(
select top 1 * from #a where Name =t.Name
) as t2 where ID=t.ID
)
drop table #a
GO
CREATE proc [dbo].[Random]
@CN varchar(50),
@QT varchar(50)
as
CREATE TABLE #a
(
[ID] [int] NOT NULL,
[CourseName] [varchar](50) NOT NULL,
[KeyPointID] [varchar](50) NOT NULL,
[QusetionType] [varchar](50) NOT NULL,
[Content] [varchar](1000) NOT NULL,
[OptionA] [varchar](100) NOT NULL,
[OptionB] [varchar](100) NOT NULL,
[OptionC] [varchar](100) NOT NULL,
[OptionD] [varchar](100) NOT NULL,
[OptionE] [varchar](100) NOT NULL,
[OptionF] [varchar](100) NOT NULL,
)
insert into #a select * from [dbo].[TB_KeyPoint] where [CourseName]=@CN and [QusetionType]=@QT order by newid() asc
select * from #a t where
exists
(
select 1 from
(
select top 1 * from #a where [KeyPointID]=t.KeyPointID
) as t2 where ID=t.ID
)
drop table #a
GO
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [id] varchar(100), [name] varchar(100));
insert #temp
select '1','a' union all
select '2','b' union all
select '3','c' union all
select '4','c' union all
select '5','c'
--SQL:
;WITH cte AS
(
select rowid=ROW_NUMBER() OVER(PARTITION BY name ORDER BY NEWID()), * from #temp
)
SELECT id, name FROM cte
WHERE rowid = 1
/*
idname
1a
2b
5c
*/