34,838
社区成员




----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-09-25 14:31:05
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([名称] varchar(3),[分类ID] int)
insert [huang]
select '甲1',1 union all
select '乙1',2 union all
select '丙1',3 union all
select '丁1',4 union all
select '戊1',5 union all
select '己1',6 union all
select '甲2',1 union all
select '乙2',2 union all
select '丙2',3 union all
select '丁2',4 union all
select '戊2',5 union all
select '己2',6 union all
select '甲3',1 union all
select '乙3',2 union all
select '丙3',3 union all
select '丁3',4 union all
select '戊3',5 union all
select '己3',6 union all
select '甲4',1 union all
select '乙4',2 union all
select '丙4',3 union all
select '丁4',4 union all
select '戊4',5 union all
select '己4',6 union all
select '甲5',1 union all
select '乙5',2 union all
select '丙5',3 union all
select '丁5',4 union all
select '戊5',5 union all
select '己5',6 union all
select '甲6',1 union all
select '乙6',2 union all
select '丙6',3 union all
select '丁6',4 union all
select '戊6',5 union all
select '己6',6
--------------开始查询--------------------------
SELECT 名称,分类id
FROM (
select * ,ROW_NUMBER()OVER(PARTITION BY 分类ID ORDER BY GETDATE())id
from [huang])a
WHERE id<=3
----------------结果----------------------------
/*
名称 分类id
---- -----------
甲1 1
甲2 1
甲3 1
乙6 2
乙5 2
乙4 2
丙1 3
丙2 3
丙3 3
丁6 4
丁5 4
丁4 4
戊1 5
戊2 5
戊3 5
己6 6
己5 6
己4 6
*/