学校-班级-学生的数据库中每个班级抽一个人如何写sql

[wnagwwzj]

假设数据结构如下：

学生id，班级id
1 101
2 101
3 101
4 101
5 102
6 102
7 201

问从每个班级id中间，随机抓一个人，如何写sql

select * from table
where 班级id=101
limit 1
这样只能抓101的1个人。
问题是想从中把每个班级抓一个人出来。

[卖水果的net]

row_number() over(partition by 班极）

[wnagwwzj]

引用 1 楼 卖水果的net 的回复:

row_number() over(partition by 班极）

额，方便写全么？

[唐诗三百首]

create table #t(学生id int,班级id int)

insert into #t(学生id,班级id)
 select 1,101 union all
 select 2,101 union all
 select 3,101 union all
 select 4,101 union all
 select 5,102 union all
 select 6,102 union all
 select 7,201


select 学生id,班级id
 from (select *,rn=row_number() over(partition by 班级id order by newid())
        from #t) t
 where t.rn=1

/*
学生id        班级id
----------- -----------
3           101
5           102
7           201

(3 row(s) affected)
*/

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[学生id] int
,[班级id] VARCHAR(10)	
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'1',N'101')
INSERT INTO dbo.[t] VALUES(N'2',N'101')
INSERT INTO dbo.[t] VALUES(N'3',N'101')
INSERT INTO dbo.[t] VALUES(N'4',N'101')
INSERT INTO dbo.[t] VALUES(N'5',N'102')
INSERT INTO dbo.[t] VALUES(N'6',N'102')
INSERT INTO dbo.[t] VALUES(N'7',N'201')

SELECT * FROM (
 SELECT ROW_NUMBER() OVER (PARTITION BY [班级id] ORDER BY NEWID()) AS rid,* FROM t
) AS tt
WHERE tt.rid=1

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([学生id] int,[班级id] int)
Insert #T
select 1,101 union all
select 2,101 union all
select 3,101 union all
select 4,101 union all
select 5,102 union all
select 6,102 union all
select 7,201
Go
--测试数据结束
select [学生id],[班级id] from (
Select *,row_number() over(partition by 班级id order by newid()) rn from #T
)t where rn=1

[砸死牛顿的苹果]

limit 应该是mysql吧 除非 mysql8.0以上支持 row_number