省市级联找子集

找到全部子集

WITH TEMP AS

(

SELECT * FROM CM_Region WHERE RegionCode='110000'

UNION ALL

SELECT T0.* FROM TEMP,CM_Region T0 WHERE TEMP.RegionCode=T0.ParentCode

)

SELECT RegionCode FROM TEMP

----------------------------------------------------------------

 

--子集拼接

SELECT ','+BusinessName FROM (SELECT T2.BusinessName FROM Sal_PromoteBusinessLine T1

LEFT JOIN CO_Business T2 ON T1.BusinessID =T2.ID

WHERE T1.PromoteID = 34) T1 FOR XML PATH('')