A表
id title
1 生活
2 娱乐
3 旅游B表
id URL Aid
1 地址1 1
2 地址2 1
3 地址3 1
4 地址4 2
5 地址5 2
需要结果
aid tile url
1 生活 地址3
2 娱乐 地址5
3 旅游 null
id title
1 生活
2 娱乐
3 旅游B表
id URL Aid
1 地址1 1
2 地址2 1
3 地址3 1
4 地址4 2
5 地址5 2
需要结果
aid tile url
1 生活 地址3
2 娱乐 地址5
3 旅游 null
from a left join b t
on a.id = t.aid and t.id = (select max(id) from b where aid = t.aid)
insert into a values(1 , '生活')
insert into a values(2 , '娱乐')
insert into a values(3 , '旅游')
create table B(id int, URL varchar(10), Aid int)
insert into b values(1 , '地址1' , 1 )
insert into b values(2 , '地址2' , 1 )
insert into b values(3 , '地址3' , 1 )
insert into b values(4 , '地址4' , 2 )
insert into b values(5 , '地址5' , 2 )
goselect a.* , t.url
from a left join b t
on a.id = t.aid and t.id = (select max(id) from b where aid = t.aid)select a.* , t.url
from a left join b t
on a.id = t.aid and not exists (select 1 from b where aid = t.aid and id > t.id)drop table a , b/*
id title url
----------- ---------- ----------
1 生活 地址3
2 娱乐 地址5
3 旅游 NULL(所影响的行数为 3 行)id title url
----------- ---------- ----------
1 生活 地址3
2 娱乐 地址5
3 旅游 NULL(所影响的行数为 3 行)*/
a.* , t.url
from
a left join b t
on
a.id = t.aid
and
t.id = (select max(id) from b where aid = t.aid)
A left join B where A.id=B.Aid
group by aid ,tile
insert into @A表
select 1,'生活' union all
select 2,'娱乐' union all
select 3,'旅游'declare @B表 table (id int,URL varchar(5),Aid int)
insert into @B表
select 1,'地址1',1 union all
select 2,'地址2',1 union all
select 3,'地址3',1 union all
select 4,'地址4',2 union all
select 5,'地址5',2select a.* ,
b.url
from @A表 a
left join ( select Aid ,
max(URL) as url
from @B表
group by Aid
) b on a.id = b.Aid/*
id title url
----------- ----- -----
1 生活 地址3
2 娱乐 地址5
3 旅游 NULL
*/
if object_id('A') is not null drop table A
create table A(id int, title varchar(10))
insert into a values(1 , '生活')
insert into a values(2 , '娱乐')
insert into a values(3 , '旅游')
if object_id('B') is not null drop table B
create table B(id int, URL varchar(10), Aid int)
insert into b values(1 , '地址1' , 1 )
insert into b values(2 , '地址2' , 1 )
insert into b values(3 , '地址3' , 1 )
insert into b values(4 , '地址4' , 2 )
insert into b values(5 , '地址5' , 2 )
--语句
select A.id as aid,title,max(URL)
from A left join B on A.id=b.aid
group by a.id,title