使用聚合函数后的再怎么对记录怎么进行查询

使用聚合函数后的再如何对记录如何进行查询？
select a,b,max(c) as c from bd_info
group by b ,a
的结果

a b c
1 2 3
1 1 1
2 2 1
2 1 2
3 1 1
3 2 3
3 1 2
3 1 3





我现在要求的，是以a列为分组的，C列值（同有最大值时，取查满足查询记录的第一行）最大的表：
a b c
1 2 3
2 1 2
3 2 3


[解决办法]

SQL code

select a,b,max(c) as c ,identity(int,1,1) as id into #t from bd_infogroup by b ,aselect * from #t t1where id  = (    select top 1 t2.id from #t t2    where t2.c=(        select max(t3.c) from #t t3        where t2.a=t3.a        ) and t1.a=t2.a)
[解决办法]
SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([a] int,[b] int,[c] int)insert [tb]select 1,2,3 union allselect 1,1,1 union allselect 2,2,1 union allselect 2,1,2 union allselect 3,1,1 union allselect 3,2,3 union allselect 3,1,2 union allselect 3,1,3 select *from tb twhere not exists(select 1 from tb where a=t.a and (c>t.c or c=t.c and b>t.b))/**a           b           c----------- ----------- -----------1           2           32           1           23           2           3(3 行受影响)**/
[解决办法]
SQL code
----------------------------------------  Author : htl258(Tony)--  Date   : 2010-04-18 09:46:23--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) --          Jul  9 2008 14:43:34 --          Copyright (c) 1988-2008 Microsoft Corporation--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)----------------------------------------> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL    DROP TABLE [tb]GOCREATE TABLE [tb]([a] INT,[b] INT,[c] INT)INSERT [tb]SELECT 1,2,3 UNION ALLSELECT 1,1,1 UNION ALLSELECT 2,2,1 UNION ALLSELECT 2,1,2 UNION ALLSELECT 3,1,1 UNION ALLSELECT 3,2,3 UNION ALLSELECT 3,1,2 UNION ALLSELECT 3,1,3GO--SELECT * FROM [tb]-->SQL查询如下:SELECT A,B,C FROM (SELECT RN=ROW_NUMBER()OVER(PARTITION BY A ORDER BY C DESC,B DESC),* FROM TB) T WHERE RN=1\/*a           b           c----------- ----------- -----------1           2           32           1           23           2           3(3 行受影响)*/
[解决办法]
SQL code
declare @temp_table table(a int,b int,c int)insert into @temp_tableselect a,b,max(c) as c from bd_infogroup by b,aselect * from @temp_table twhere not exists(select 1 from @temp_table where a=t.a and (c=t.c and b>t.b or c>t.c))