一个表一列与另一个表多列查询

select c.serial_number,b.mold_no,MAX(c.apply_time ) as apply_time ,
stuff(
(
select ','+wm_apply_mac_reason.apply_reason from wm_apply_mac_reason where serial_number=a.serial_number for xml path('')
)
,1,1,'')
 as apply_reason, max(c.apply_name),c.apply_bz,
 stuff(
(
select ','+wm_reality_mac_reason.reality_reason from wm_reality_mac_reason where serial_number=b.serial_number for xml path('')
)
,1,1,'')
 as reality_reason, c.reality_name,MAX(c.reality_time) as reality_time,c.reality_bz
 from wm_apply_mac_reason a,wm_reality_mac_reason b,wm_repair_mac_order c where a.serial_number = b.serial_number and b.serial_number=c.serial_number and (c.apply_time >= '2013-10-23 20:23:02.190' and c.apply_time <= '2013-10-26 20:23:02.190')  group by a.serial_number,b.mold_no,b.serial_number,c.serial_number,c.apply_bz,c.reality_name,c.reality_bz
order by c.serial_number