表A:
ID INFO
1 man
2 woman
3 boy
4 girl表B:
ID NAME
1 Jack
1 Mike
3 Sam
2 Lucy
2 Lily要求得到:
ID NAME1 NAME2 INFO
1 Jack Mike man
2 Lucy Lily woman
3 Sam boy
4 girl注:一个ID对应的NAME不会超过两个
ID INFO
1 man
2 woman
3 boy
4 girl表B:
ID NAME
1 Jack
1 Mike
3 Sam
2 Lucy
2 Lily要求得到:
ID NAME1 NAME2 INFO
1 Jack Mike man
2 Lucy Lily woman
3 Sam boy
4 girl注:一个ID对应的NAME不会超过两个
from a t1 left join
(select ID,min(NAME) as name from b group by id) b on t1.id = t2.id
left join
(select id,name from b t1 where name <> (select min(name) from b where id = t1.id))t3
on t1.id = t3.id
create table 表B(ID int, NAME varchar(10))
insert into 表A
select 1, 'man'
union all select 2, 'woman'
union all select 3, 'boy'
union all select 4, 'girl'insert into 表B
select 1, 'Jack'
union all select 1, 'Mike'
union all select 3, 'Sam'
union all select 2, 'Lucy'
union all select 2, 'Lily'select A.id,
isnull((select min(name) from 表B where ID = A.ID), '') as name1,
isnull((select max(name) from 表B where ID = A.ID having count(1) > 1), '') as name2
from 表A Adrop table 表A
drop table 表B