请教sql语句
表A
id num bid
1 3 2
2 4 2
3 1 2
4 5 3
5 6 4
表B
id name
1 nick
2 mily
3 nono
条件一:表 A中bid必须在表B中id存在
条件二:查询按bid分组,A.num最大值时的A.id和A.num的最大值
查询结果如下:
A.id A.num
2 4
4 5
表A
id num bid
1 3 2
2 4 2
3 1 2
4 5 3
5 6 4
表B
id name
1 nick
2 mily
3 nono
条件一:表 A中bid必须在表B中id存在
条件二:查询按bid分组,A.num最大值时的A.id和A.num的最大值
查询结果如下:
A.id A.num
2 4
4 5
已写入 file afiedt.buf 1 with Atb as(
2 select 1 id,3 num,2 bid from dual
3 union all
4 select 2,4,2 from dual
5 union all
6 select 3,1,2 from dual
7 union all
8 select 4,5,3 from dual
9 union all
10 select 5,6,4 from dual),
11 Btb as(
12 select 1 id, 'nick' name from dual
13 union all
14 select 2,'mily' from dual
15 union all
16 select 3,'nono' from dual)
17 select max(Atb.id),max(Atb.num)
18 from Atb,Btb
19 where Atb.id=Btb.id
20* group by Atb.bid
SQL> /MAX(ATB.ID) MAX(ATB.NUM)
----------- ------------
3 4
已写入 file afiedt.buf 1 with Atb as(
2 select 1 id,3 num,2 bid from dual
3 union all
4 select 2,4,2 from dual
5 union all
6 select 3,1,2 from dual
7 union all
8 select 4,5,3 from dual
9 union all
10 select 5,6,4 from dual),
11 Btb as(
12 select 1 id, 'nick' name from dual
13 union all
14 select 2,'mily' from dual
15 union all
16 select 3,'nono' from dual)
17 select max(Atb.id),max(Atb.num)
18 from Atb,Btb
19 where Atb.bid=Btb.id
20* group by Atb.bid
SQL> /MAX(ATB.ID) MAX(ATB.NUM)
----------- ------------
3 4
4 5
select max(Atb.id),max(Atb.num)
from Atb,Btb
where Atb.bid=Btb.id
group by Atb.bid
--1
select * from ta a
where exists(select 1 from tb b
where a.bid=b.id)
--2
select * from ta a
where exists(select 1 from tb b
where a.bid=b.id)
and not exists(select 1 from ta c
where a.bid=c.bid and a.num<c.num)
(select 1 id, 3 num, 2 bid
from dual
union all
select 2, 4, 2
from dual
union all
select 3, 1, 2
from dual
union all
select 4, 5, 3
from dual
union all
select 5, 6, 4 from dual),
Btb as
(select 1 id, 'nick' name
from dual
union all
select 2, 'mily'
from dual
union all
select 3, 'nono' from dual)
select id, num
from (select a.bid,
a.id,
a.num,
row_number() over(partition by a.bid order by num desc) rn
from Atb a, Btb b
where a.bid in (select id from Btb))
where rn = 1
select max(a.id),a.bid from a where a.bid in (select id from b) group by bid;