多表使用分组Group By,该怎么处理

--> 测试数据：[tbla]
if object_id('[tbla]') is not null drop table [tbla]
create table [tbla]([a_id] int,[a_name] varchar(4))
insert [tbla]
select 1,'苹果' union all
select 2,'橘子' union all
select 3,'香蕉'
--> 测试数据：[tblb]
if object_id('[tblb]') is not null drop table [tblb]
create table [tblb]([b_id] int,[b_name] varchar(6),[b_num] int,[a_id] int)
insert [tblb]
select 1,'苹果一',10,1 union all
select 2,'苹果二',20,1 union all
select 3,'橘子',30,2 union all
select 4,'香蕉一',40,3 union all
select 5,'香蕉二',50,3


select d.[b_id],e.* from(
select [a_name],MAX([b_num]) [b_num] from(
select b.[b_id],a.[a_name],b.[b_num] from tblb b
full join tbla a on a.a_id=b.a_id)c
group by [a_name])e inner join (select b.[b_id],a.[a_name],b.[b_num] from tblb b
full join tbla a on a.a_id=b.a_id)d on d.a_name=e.a_name and d.b_num=e.b_num

/*
b_id    a_name    b_num
2    苹果    20
3    橘子    30
5    香蕉    50
*/

------解决方案--------------------
这个问题我有点质疑，如果苹果一和苹果二的价格都是20，而20都是最高价，结果会怎么样
2楼的结果看来，好像不用这么复杂的语句吧

SQL code

select a.a_id, a.a_name, max(b.b_sum)
from tbla a
    inner join tblb b
        on a.a_id = b.a_id
group by a.a_id, a.a_name
order by a.a_id

------解决方案--------------------

SQL code

select * from 产品详细表 a where not exists(select * from 产品详细表 b where  a.a_id =b.a_id and a.b_num <b.b_num )

------解决方案--------------------
select * from b b1 not exists
(
  select 1 from b b2 where b1.a_id=b2.a_id and b1.b_num<b2.b_num
)