stuff使用感悟

 

select 
STUFF((select ','+Convert(varchar(10),t2.CityId) from t t2 where not exists(
select top 1 1 from Web_UserCity uc where UserName='user001' and uc.CityId=t2.CityId
 ) for xml path('')) ,1,1,'')
cityIds2
from t
where not exists(
select top 1 1 from Web_UserCity uc where UserName='user001' and uc.CityId=t2.CityId
 )

注意假如表中只有一个字段时，写stuff时，不要盲目乱加条件。