22,300
社区成员




;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
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
--> 测试数据:[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
select
*
from
(select px=row_number()over(partition by username order by newid()),* from tb)t
where px<=5 and username=N'张三'