select id, aid, aname=(select top 1 aname from tt2 where aid=a.aid) from tt1 a group by id,aid
--测试环境 create table tt1 (id int, aid int) create table tt2 (aid int, aname char) insert tt1 select 1,3 union all select 2,4 union all select 3,5 union all select 4,2 insert tt2 select 2,'a' union all select 2,'b' union all select 3,'c' union all select 4,'d' union all select 4,'e' union all select 5,'f'
select a.id,a.aid,b.aname from a,b where a.id=b.aid
create table tt1 (id int, aid int) create table tt2 (aid int, aname char) insert tt1 select 1,3 union all select 2,4 union all select 3,5 union all select 4,2 insert tt2 select 2,'a' union all select 2,'b' union all select 3,'c' union all select 4,'d' union all select 4,'e' union all select 5,'f' select id,aid,min(aname) as aname from (select tt1.id,tt1.aid,tt2.aname from tt1,tt2 where tt1.aid=tt2.aid) t group by id,aid order by id
select id,
aid,
aname=(select top 1 aname from tt2 where aid=a.aid)
from tt1 a
group by id,aid
create table tt1
(id int,
aid int)
create table tt2
(aid int,
aname char)
insert tt1
select 1,3 union all
select 2,4 union all
select 3,5 union all
select 4,2
insert tt2
select 2,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 4,'e' union all
select 5,'f'
from a,b
where a.id=b.aid
(id int,
aid int)
create table tt2
(aid int,
aname char)
insert tt1
select 1,3 union all
select 2,4 union all
select 3,5 union all
select 4,2
insert tt2
select 2,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 4,'e' union all
select 5,'f'
select id,aid,min(aname) as aname from (select tt1.id,tt1.aid,tt2.aname from tt1,tt2 where tt1.aid=tt2.aid) t
group by id,aid order by id