请大家帮我看看这个查询怎么写

[haochin]

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

[LongRui888]

如果是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)
*/

[唐诗三百首]

方法2,

select a.c1,a.c2,a.c3
 from tb1 a
 where not exists
 (select 1 from tb1 b where b.c1=a.c1 and b.c2>a.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
*/

[發糞塗牆]

还有一种：

----------------------------------------------------------------
-- 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 *
from [tb1] a
WHERE EXISTS(SELECT 1 FROM (SELECT c1,MAX(c2)c2 FROM tb1 GROUP BY c1) b WHERE a.c1=b.c1 AND a.c2=b.c2
)
----------------结果----------------------------
/* 
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

[以学习为目的]

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 WHERE c2=(SELECT MAX(c2) FROM [tb1] WHERE a.c1=c1)ORDER BY c1
*************************
c1    c2     c3
a	3	CC
b	2	CA
c	3	CF
d	4	TR
************************

[Miss胡不归]

学习了 原来可以这样

[以学习为目的]

只能连续回复3次，终于可以再发三次了

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 rankId=rank() over(partition by c1 order by c2 desc),* from [tb1])a
WHERE a.rankId='1'

[诺维斯基]

引用 2 楼 ap0405140 的回复:

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)
*/

我同意这个。