select top 1 [group],score=avg(score) from table1 a join table2 b on a.name=b.name group by [group] order by score desc
--下面是测试 declare @table1 table(name char(8),score int) declare @table2 table(name char(8),[group] char(6))insert into @table1 select 'li',70 union all select 'wu',67 union all select 'zhou',80 union all select 'zhang',56insert into @table2 select 'li','AAA' union all select 'wu','BBB' union all select 'zhou','AAA' union all select 'zhang','BBB'--查询 select top 1 [group],score=avg(score) from @table1 a join @table2 b on a.name=b.name group by [group] order by score desc/*--结果 group score ------ ----------- AAA 75 --*/
select top 1 b.group from table1 a join table2 b on a.name=b.name group by b.group order by avg(a.score) desc注意,不要用[name]关联,因为有重名的可能。
from table1 a join table2 b on a.name=b.name
group by [group]
order by score desc
declare @table1 table(name char(8),score int)
declare @table2 table(name char(8),[group] char(6))insert into @table1
select 'li',70
union all select 'wu',67
union all select 'zhou',80
union all select 'zhang',56insert into @table2
select 'li','AAA'
union all select 'wu','BBB'
union all select 'zhou','AAA'
union all select 'zhang','BBB'--查询
select top 1 [group],score=avg(score)
from @table1 a join @table2 b on a.name=b.name
group by [group]
order by score desc/*--结果
group score
------ -----------
AAA 75
--*/
from table1 a join table2 b on a.name=b.name
group by b.group
order by avg(a.score) desc注意,不要用[name]关联,因为有重名的可能。