日期:2014-05-17 浏览次数:20672 次
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-06 14:02:31
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([第一列] varchar(2),[第二列] varchar(2),[第三列] varchar(1))
insert [huang]
select 'A1','B1','C' union all
select 'A2','B2','C' union all
select 'A3','B3','C' union all
select 'A4','B4','D' union all
select 'A5','B5','D' union all
select 'A6','B6','D'
--------------开始查询--------------------------
SELECT [第一列],[第二列],[第三列]
FROM (
select *,ROW_NUMBER()OVER(PARTITION BY [第三列] ORDER BY [第一列]) id
from [huang]
)a
WHERE id<=2
----------------结果----------------------------
/*
第一列 第二列 第三列
---- ---- ----
A1 B1 C
A2 B2 C
A4 B4 D
A5 B5 D
*/
with tb(a,b,c)as(
select 'a1','b1','c' union all
select 'a2','b2','c' union all
select 'a3','b3','c' union all
select 'a4','b4','d' union all