62,243
社区成员




create table #a(
id int,
name varchar(50)
)
insert into #a(id,name) values(10,'aa')
insert into #a(id,name) values(11,'bb')
insert into #a(id,name) values(12,'cc')
insert into #a(id,name) values(13,'dd')
create table #b(
id int,
statuss int
)
insert into #b(id,statuss) values(10,3)
insert into #b(id,statuss) values(11,4)
insert into #b(id,statuss) values(11,3)
insert into #b(id,statuss) values(10,5)
--#b表 statuss 最大值
select MAX(statuss) statuss,id into #c from #b group by id
select a.name, c.id,c.statuss from #a a inner join #c c on
a.id=c.id
--where b.statuss=3
drop table #a
drop table #b
drop table #c
select
A.aid,A.name,B.status
from
A
left join
(select max(bid) bid,aid,status from B group by aid,status)B on A.aid=B.aid
where
B.status=3
select
top 1 A.aid,A.name,B.status
from
A
left join
B on A.aid=B.aid
where
B.status=3
order by
B.bid desc
select
A.aid,A.name,B.status
from
A
left join
B on A.aid=B.aid
where
B.status=3
select b.aid,a.name,b.status from a inner join b on a.aid=b.aid
where b.status=3