tb1
c1 c2 c3
a 1 AA
a 2 BB
a 3 CC
b 1 CD
b 2 CA
c 1 AA
c 3 CF
d 4 TR
查出每组c1中c2最大的记录
结果如下：
c1 c2
a 3 CC
b 2 CA
c 3 CF
d 4 TR
------解决方案--------------------
如果是2005，可以试试这个：
select c1 , c2 , c3
from
(
select *,row_number() over(partition by c1 order by c2 desc) as rownum
from tb1
)t
where rownum = 1
------解决方案--------------------
create table tb1
(c1 varchar(5),c2 int,c3 varchar(5))
insert into tb1
select 'a',1,'AA' union all
select 'a',2,'BB' union all
select 'a',3,'CC' union all
select 'b',1,'CD' union all
select 'b',2,'CA' union all
select 'c',1,'AA' union all
select 'c',3,'CF' union all
select 'd',4,'TR'
select a.c1,a.c2,a.c3
from tb1 a
inner join
(select c1,max(c2) 'c2'
from tb1
group by c1) b on a.c1=b.c1 and a.c2=b.c2
order by a.c1
/*
c1 c2 c3
----- ----------- -----
a 3 CC
b 2 CA
c 3 CF
d 4 TR
(4 row(s) affected)
*/
------解决方案--------------------
我来个变态点的：
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-16 14:48:16
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[tb1]
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([c1] nvarchar(2),[c2] int,[c3] nvarchar(4))
insert [tb1]
select 'a',1,'AA' union all
select 'a',2,'BB' union all
select 'a',3,'CC' union all
select 'b',1,'CD' union all
select 'b',2,'CA' union all
select 'c',1,'AA' union all
select 'c',3,'CF' union all
select 'd',4,'TR'
--------------生成数据--------------------------
SELECT c1,c2,c3
FROM (
select * ,MAX(c2)OVER(PARTITION BY c1)rn
from [tb1])a
WHERE c2=rn
----------------结果----------------------------
/*
c1 c2 c3
---- ----------- ----
a 3 CC
b 2 CA
c 3 CF
d 4 TR
*/
------解决方案--------------------
来一个非主流一点的
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([c1] nvarchar(2),[c2] int,[c3] nvarchar(4))
insert [tb1]
select 'a',1,'AA' union all
select 'a',2,'BB' union all
select 'a',3,'CC' union all
select 'b',1,'CD' union all
select 'b',2,'CA' union all
select 'c',1,'AA' union all
select 'c',3,'CF' union all
select 'd',4,'TR'
SELECT * FROM [tb1] a GROUP BY c1,c2,c3
HAVING c2=(SELECT MAX(c2) FROM [tb1] WHERE a.c1=c1)ORDER BY c1