select
*
from
(select px=row_number()over(partition by username order by newid()),* from tb)t
where
px<=5
SELECT *
FROM (
SELECT row_id= row_number ()OVER (PARTITION BY username ORDER BY NEWID ()), * FROM tb
) t
WHERE row_id <= 5
select
*
from
(select px=row_number()over(partition by username order by newid()),* from tb)t
where px<=5 and username=N'张三'
--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[name] VARCHAR(4))
INSERT [tb]
SELECT 1,'张三' UNION ALL
SELECT 2,'张三' UNION ALL
SELECT 3,'张三' UNION ALL
SELECT 4,'张三' UNION ALL
SELECT 5,'张三' UNION ALL
SELECT 6,'张三' UNION ALL
SELECT 7,'李四' UNION ALL
SELECT 8,'李四' UNION ALL
SELECT 9,'李四' UNION ALL
SELECT 10,'李四' UNION ALL
SELECT 11,'李四' UNION ALL
SELECT 12,'李四' UNION ALL
SELECT 1,'王五' UNION ALL
SELECT 2,'王五' UNION ALL
SELECT 3,'王五' UNION ALL
SELECT 4,'王五' UNION ALL
SELECT 5,'王五' UNION ALL
SELECT 6,'王五' UNION ALL
SELECT 7,'王五'
--------------开始查询--------------------------
SELECT *
FROM (
SELECT *, row_id= row_number () OVER (PARTITION BY NAME ORDER BY NEWID ()) FROM [tb]
) AS t
WHERE row_id <= 5
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[name] VARCHAR(4))
INSERT [tb]
SELECT 1,'张三' UNION ALL
SELECT 2,'张三' UNION ALL
SELECT 3,'张三' UNION ALL
SELECT 4,'张三' UNION ALL
SELECT 5,'张三' UNION ALL
SELECT 6,'张三' UNION ALL
SELECT 7,'李四' UNION ALL
SELECT 8,'李四' UNION ALL
SELECT 9,'李四' UNION ALL
SELECT 10,'李四' UNION ALL
SELECT 11,'李四' UNION ALL
SELECT 12,'李四' UNION ALL
SELECT 1,'王五' UNION ALL
SELECT 2,'王五' UNION ALL
SELECT 3,'王五' UNION ALL
SELECT 4,'王五' UNION ALL
SELECT 5,'王五' UNION ALL
SELECT 6,'王五' UNION ALL
SELECT 7,'王五'
--------------开始查询--------------------------
SELECT *
FROM
(SELECT *, rn= row_number () OVER (PARTITION BY USERNAME ORDER BY NEWID ()) FROM [tb]
) AS t
WHERE rn <= 5
;with aaa as
(
select row_number() over(partition by username order by newid()) as rowindex,*
from tb with(nolock)
)
select * from aaa where rowindex<5
