a表:
aid title content
1 w1 www
2 w2 www
3 w3 sss
4 w4 jjjj
5 jj jouob表:
bid name aid
1 u1 1
2 jo 1
3 u2 2
4 u3 3
5 oo 3
6 jo 3
7 u4 4
8 u5 5
9 jo 5从两个表查询后的结果:
title content name
w1 www u1
w2 www u2
w3 sss u3
w4 jjjj u4
jj jouo u5请问这样的sql语句如何写?谢谢
aid title content
1 w1 www
2 w2 www
3 w3 sss
4 w4 jjjj
5 jj jouob表:
bid name aid
1 u1 1
2 jo 1
3 u2 2
4 u3 3
5 oo 3
6 jo 3
7 u4 4
8 u5 5
9 jo 5从两个表查询后的结果:
title content name
w1 www u1
w2 www u2
w3 sss u3
w4 jjjj u4
jj jouo u5请问这样的sql语句如何写?谢谢
--> 测试数据: @a
declare @a table (aid int,title varchar(2),content varchar(4))
insert into @a
select 1,'w1','www' union all
select 2,'w2','www' union all
select 3,'w3','sss' union all
select 4,'w4','jjjj' union all
select 5,'jj','jouo'
--> 测试数据: @b
declare @b table (bid int,name varchar(2),aid int)
insert into @b
select 1,'u1',1 union all
select 2,'jo',1 union all
select 3,'u2',2 union all
select 4,'u3',3 union all
select 5,'oo',3 union all
select 6,'jo',3 union all
select 7,'u4',4 union all
select 8,'u5',5 union all
select 9,'jo',5select title,content,b.name from @a a,@b b
where a.aid=b.aid and not exists(select 1 from @b where aid=b.aid and bid<b.bid)
insert into @a
select 1,'w1','www' union all
select 2,'w2','www' union all
select 3,'w3','sss' union all
select 4,'w4','jjjj' union all
select 5,'jj','jouo'
--> 测试数据: @b
declare @b table (bid int,name varchar(2),aid int)
insert into @b
select 1,'u1',1 union all
select 2,'jo',1 union all
select 3,'u2',2 union all
select 4,'u3',3 union all
select 5,'oo',3 union all
select 6,'jo',3 union all
select 7,'u4',4 union all
select 8,'u5',5 union all
select 9,'jo',5select a.*,b.name
from @a a
left join (select * from @b a
where not exists(select 1 from @b where aid = a.aid and bid < a.bid) ) b
on a.aid = b.aid
aid title content name
----------- ----- ------- ----
1 w1 www u1
2 w2 www u2
3 w3 sss u3
4 w4 jjjj u4
5 jj jouo u5(所影响的行数为 5 行)