表a
bid column1 column 2
1 aaa bbb
2 ccc ddd
表b
id bid column3 column4
1 1 eeeee ffff
2 1 ggggg hhh
3 2 uuuu iii
我要显示的效果
bid column1 column2 column3 column4
1 aaa bbb eeeee ffff
2 ccc ddd uuuu iii
也就是不要显示表b中有重复的bid
bid column1 column 2
1 aaa bbb
2 ccc ddd
表b
id bid column3 column4
1 1 eeeee ffff
2 1 ggggg hhh
3 2 uuuu iii
我要显示的效果
bid column1 column2 column3 column4
1 aaa bbb eeeee ffff
2 ccc ddd uuuu iii
也就是不要显示表b中有重复的bid
select a.*,b.column3, b.column4 from
a inner join (
select * from b a where not exists( select 1 from b where a.bid=bid and a.id>id))b
on a.bid=b.bid
insert @ta
select 1, 'aaa', 'bbb' union all
select 2, 'ccc', 'ddd'
declare @tb table(id int, bid int,column3 varchar(10), column4 varchar(10))
insert @tb
select 1, 1, 'eeeee', 'ffff' union all
select 2, 1, 'ggggg', 'hhh' union all
select 3, 2, 'uuuu', 'iii' select a.*,b.column3,b.column4 from @ta as a
left join
(select * from @tb as t where not exists(select 1 from @tb where bid = t.bid and id < t.id)) as b
on a.bid = b.bid/*结果
bid column1 column2 column3 column4
--------------------------------------
1 aaa bbb eeeee ffff
2 ccc ddd uuuu iii
*/
SQL语句:
select a.*,b.column3,b.column4 from 表A as a
left join
(select * from 表B as t where not exists(select 1 from 表B where bid = t.bid and id < t.id)) as b
on a.bid = b.bid