22,210
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,area varchar(50),[Code] int)
Insert #T
select 1,'AA',10001 union all
select 2,'AA',10002 union all
select 3,'AA',10003 union all
select 4,'AA',10004 union all
select 5,'AA',10003 union all
select 6,'BB',10004 union all
select 7,'BB',10003 union all
select 8,'BB',10004 union all
select 9,'BB',10005
;
WITH LIST AS(
SELECT *,RID=ROW_NUMBER()OVER(PARTITION BY area ORDER BY NEWID())
FROM #T
)
SELECT *
FROM LIST
WHERE RID<=2
ID area Code RID
----------- -------------------------------------------------- ----------- --------------------
2 AA 10002 1
4 AA 10004 2
9 BB 10005 1
7 BB 10003 2
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Code] int)
Insert #T
select 1,10001 union all
select 2,10002 union all
select 3,10003 union all
select 4,10004 union all
select 5,10005
Go
--测试数据结束
Select TOP 2 * from #T ORDER BY NEWID()