【请教一条分组查询详细信息的SQL语句】
第一列 第二列 第三列
A1 B1 C
A2 B2 C
A3 B3 C
A4 B4 D
A5 B5 D
A6 B6 D
我想要根据第三列分组分别查询出对应第三列的前2条数据
即得到如下结果：
A1 B1 C
A2 B2 C
A4 B4 D
A5 B5 D
请问这样的SQL语句该怎么写，谢谢…
[解决办法]
----------------------------
-- 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
select 'a5','b5','d' union all
select 'a6','b6','d'
)
select a,b,c from (select *,number=ROW_NUMBER() over(partition by c order by a) from tb)a
where number<3
create table hap
(第一列 varchar(5),
第二列 varchar(5),
第三列 varchar(5))
insert into hap
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 hap a
where (select count(1)
from hap b
where b.第三列=a.第三列
and b.第一列<=a.第一列
)<=2
/*
第一列 第二列 第三列
----- ----- -----
A1 B1 C
A2 B2 C
A4 B4 D
A5 B5 D
(4 row(s) affected)
*/
