select a.*,b.Organization_Name,c.User_Name sgry,c.renNum,d.User_Name fzr,e.pic_url from dbo.TB_ZYM_LOG a
left join dbo.Base_Organization b on a.sgdw_id=b.Organization_ID
left join (
select log_id,COUNT(USER_NAME) renNum,User_Name=
stuff(
(
SELECT ',' + User_Name FROM V_ZYM_Log_CO AS t WHERE t.log_id = V_ZYM_Log_CO.log_id FOR xml path('')
), 1, 1, '') from V_ZYM_Log_CO GROUP BY log_id
) c on a.log_id=c.log_id
left join Base_UserInfo d on a.UserID=d.User_ID
left join(
select log_id,pic_url=
STUFF(
(
select '|'+pic_url from TB_ZYM_LOG_Image as u where u.log_id=TB_ZYM_LOG_Image.log_id FOR xml path('')
),1,1,'') from TB_ZYM_LOG_Image group by log_id
) e on a.log_id=e.log_id
where a.JH_ID=28
