select a.* , b.id from a inner join b on a.id = b.aid where b.aid in ( select top 10 a.id FROM a ) order by a.id
找了好长时间 终于等到正确答案了select * from PE_Interview left join (select Interview_ID, [names]=stuff((select ','+[name] from PE_Interview_Guest t where Interview_ID=PE_Interview_Guest.Interview_ID for xml path('')), 1, 1, '') from PE_Interview_Guest group by Interview_ID) guest on(PE_Interview.id=guest.Interview_ID)
select a.* , b.Name from a , b where a.id = b.aid
order by a.id
select a.*,b.namefrom a left join b on a.id=b.id
order by a.id
select top 10 a.*,b.name from a right join b on a.id=b.id
order by a.id
select ID,Name,Add,Tel,Time, AID,Name from a left join b on a._id=b.a_id
select top 10 a.* , b.* from a , b where a.id = b.aid order by a.id
但是如果B表中没有A的相应的记录
这样A表的那条记录就会被剔除了如果B表中不存在A的相应的记录,也要查询出来
select a.* , b.id from a inner join b on a.id = b.aid where b.aid in ( select top 10 a.id FROM a ) order by a.id
终于等到正确答案了select * from PE_Interview left join
(select Interview_ID, [names]=stuff((select ','+[name] from PE_Interview_Guest t where Interview_ID=PE_Interview_Guest.Interview_ID for xml path('')), 1, 1, '')
from PE_Interview_Guest
group by Interview_ID) guest on(PE_Interview.id=guest.Interview_ID)