mssql stuff 使用

CREATE view [dbo].[v_qua_rsmaininfo] as
   select '' printuser,a.bid,datediff(day,a.residedate,a.planleavedate+1) residedays,a.mealid,q.mealname,c.jysnote,c.cynote,c.chnote,c.hlnote,c.ebnote,c.bzgynote,
   w.bname,case b.ispc when -1 then c.ispc else b.ispc end ispc,case b.istr when -1 then c.istr else b.istr end istr,
   c.idcard ordidcard,a.phone curphone,x.username adviser,d.babyname,d.babysex,isnull(b.jg,a.jg) jg,
   d.weight,d.jaundicevalue,d.babyremark,f.acname intimes,g.acname deliverytimes,replace(c.remark,CHAR(13) + CHAR(10),'') ordremark,dbo.f_crm_getedcweekbyresidedate(a.childbirthday,a.residedate) edcweek,
   h.acname emergencyship,j.acname Clothing,k.acname Shoes,l.acname mamafood,b.cuid,b.cuname,b.cusnation,(case b.fmtype when 1 then '剖腹产' else '顺产' end) fmtype
   ,b.Phone,a.childbirthday,b.emergencyman,b.emergencyphone,b.fetalno,a.residedate,a.planleavedate + 1 planleavedate,b.isneedcar,b.departuretime,b.departureaddress,b.breastcondition
   ,b.arrivaltime,b.residepropaganda,b.demandbefore,b.specialcase,b.educationlevel,b.cujob,b.scarno,
   b.Status,b.Remarks,b.itemsneeded,b.qrurl,b.residetime,b.CarDestination,b.idcard,b.othername,b.istaboofood,b.isfamilymeal from tn_bas_customer a 
   join tn_Qua_rsMain b on b.cuid = a.cuid and b.btype = 1 and b.status = 2
   join tn_crm_order_h c on c.cuid = a.cuid
   left join (select entryid, 
    [babyname]=stuff((select distinct ','  + babyname  from tn_Qua_rsBabyinfo b  where b.entryid = a.entryid   for xml path('')),1,1,''),
    [babyremark]=stuff((select distinct ','  + remarks  from tn_Qua_rsBabyinfo g  where g.entryid = a.entryid   for xml path('')),1,1,''),
    [babysex]=stuff((select distinct ',' +babysex from(select entryid, (case isnull(sex,0) when 0 then '男'  else '女' end)  babysex from tn_Qua_rsBabyinfo) c where c.entryid = a.entryid  for xml path('')),1,1,''),
    [weight]=stuff((select ',' +weight from( select entryid,cast(cast(round(weight,2) as float) as varchar(20))  weight from tn_Qua_rsBabyinfo)  d where d.entryid = a.entryid  for xml path('')),1,1,''),
    [jaundicevalue]=stuff((select distinct ',' + jaundicevalue from(select entryid, (case isnull(jaundicevalue,0) when 0 then '正常'  else '偏高' end)  jaundicevalue from tn_Qua_rsBabyinfo) f where f.entryid = a.entryid  for xml path('')),1,1,'')
     from tn_Qua_rsBabyinfo a  where entryid = a.entryid   group by entryid ) d on b.entryid = d.entryid
   left join tn_Qua_QuestionnaireValue f on f.avalue = b.intimes  and f.qnid =41
   left join tn_Qua_QuestionnaireValue g on g.avalue = b.deliverytimes  and g.qnid =42
   left join tn_Qua_QuestionnaireValue h on h.avalue = b.emergencyship  and h.qnid =44
   left join tn_Qua_QuestionnaireValue j on j.avalue = b.Clothing  and j.qnid =48
   left join tn_Qua_QuestionnaireValue k on k.avalue = b.Shoes  and k.qnid =49
   left join tn_Qua_QuestionnaireValue l on l.avalue = b.mamafood  and l.qnid =50
   left join tn_bas_meal_h q on q.sheetid = a.mealid
   left join tn_sys_branch w on w.bid = a.bid
   left join tn_sys_user x on x.userid = a.adviser