select A.class_id,
A.class_name,
B.class_tuition
from A left join
(select class_id,max(class_tuition)class_tuition
from B
group by class_id )B
on A.class_id = B.class_id
A.class_name,
B.class_tuition
from A left join
(select class_id,max(class_tuition)class_tuition
from B
group by class_id )B
on A.class_id = B.class_id
select class_id,class_name,class_tuition
from
(
select A.class_id,A.class_name,B.class_tuition,
ROW_NUMBER() over(partition by A.class_id
order by B.class_tuition desc) rownum
from A
left join B
on A.class_id = B.class_id
)t
where rownum = 1
select class_id,class_name,class_tuition1,class_tuition2
from
(
select A.class_id,A.class_name,B.class_tuition1,
B.class_tuition2,
ROW_NUMBER() over(partition by A.class_id
order by B.class_tuition1 desc) rownum
from A
left join B
on A.class_id = B.class_id
)t
where rownum = 1
from a
left join
(
select b.class_id,b.class_tuition1,b.class_tuition2
from b
inner join
(
select class_id,MAX(class_tuition1) as class_tuition1
from b
group by class_id
)t
on b.class_id = t.class_id and b.class_tuition1 = t.class_tuition1
)
b
on b.class_id= a.class_id
/*
class_id class_name class_tuition1 class_tuition2
1401 数学 300 400
1402 物理 250 800
1403 化学 NULL NULL
*/
--gocreate table A(class_id int,class_name varchar(10))insert into a
select 1401 ,'数学' union all
select 1402 ,'物理' union all
select 1403 ,'化学' create table B(
ID int,class_id int,class_tuition1 int,class_tuition2 int)insert into b
select 1 ,1401 ,200 ,500 union all
select 2 ,1401 ,300 ,400 union all
select 3 ,1402 ,250 ,800
go
select a.class_id,a.class_name,b.class_tuition1,b.class_tuition2
from a
left join
(
select b.class_id ,b.class_tuition1,b.class_tuition2
from b
inner join
(
select class_id,MAX(id) id
from b
group by class_id
)t
on b.class_id = t.class_id and b.id = t.id
)b
on a.class_id = b.class_id
/*
class_id class_name class_tuition1 class_tuition2
1401 数学 300 400
1402 物理 250 800
1403 化学 NULL NULL
*/
select a.class_id,a.class_name,b.class_tuition1,b.class_tuition2
from a left join
(
select * from b t1 where ID=(select MAX(ID) from b where class_id=t1.class_id)
) b
on a.class_id=b.class_id