--sql中根据逗号分隔，查出多行数据

select 

     a.DiscussID,b.LocationID

 from 

     (select DiscussID,LocationID=convert(xml,' <root> <v>'+replace(LocationID,',',' </v> <v>')+' </v> </root>') from SG_Discuss)a

 outer apply

     (select LocationID=C.v.value('.','nvarchar(100)') from a.LocationID.nodes('/root/v')C(v))b

 

--查出多行之后计算和

select DiscussID,SUM(L.CalcRentArea) CalcRentArea from (select 

     a.DiscussID,b.LocationID

 from 

     (select DiscussID,LocationID=convert(xml,' <root> <v>'+replace(LocationID,',',' </v> <v>')+' </v> </root>') from SG_Discuss)a

 outer apply

     (select LocationID=C.v.value('.','nvarchar(100)') from a.LocationID.nodes('/root/v')C(v))b) A inner join MALL_Location l

ON A.LocationID=L.LocationID group by DiscussID