table a:
a_name
a001
a002
a003table 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
a_name
a001
a002
a003table 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
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