日期:2014-05-17  浏览次数:20628 次

求教一个sql语句?
table   a:
a_name
a001
a002
a003

table   b:
a_name   b_id
a001       1
a001       2
a002       3
a002       2

求一个sql语句,返回a中所有的记录和相应的max(b_id),结果:
a_name     b_id
a001         2
a002         3
a003         0



------解决方案--------------------
create table a( a_name varchar2(100));
insert into a
select 'a001 ' from dual
union
select 'a002 ' from dual
union
select 'a003 ' from dual;


create table b( a_name varchar2(100),b_id int);
insert into b
select 'a001 ',1 from dual
union
select 'a001 ',2 from dual
union
select 'a002 ',3 from dual
union
select 'a002 ',2 from dual;
///
select a_name ,max(b_id) from(
select a.a_name,case when b.b_id is null then 0 else b.b_id end b_id from a
left join b on a.a_name=b.a_name
)
group by a_name
--Result
a_name b_id
a001 2
a002 3
a003 0