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

最新推荐文章于 2024-03-24 19:49:34 发布

weixin_30666943

最新推荐文章于 2024-03-24 19:49:34 发布

阅读量788

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/heyiping/p/9755307.html

版权

--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

转载于:https://www.cnblogs.com/heyiping/p/9755307.html