if object_id('[a]') is not null drop table [a]
go
create table [a]([id] int, [dm] nvarchar(30),[cityId] varchar(30))
go
insert into [a]
select 1, N'新疆', '1,2' union all
select 2, N'西藏', '2,3,4'
go
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int, [cityName] nvarchar(30))
go
insert into [b]
select 1, N'乌鲁木齐' union all
select 2, N'成都' union all
select 3, N'包头' union all
select 4, N'东胜'
go

select * from [a]

select [a].[id],[a].[dm],[b].[cityName],[b].[id] cityId from [b],[a]
where charindex(convert(varchar(5),[b].[id])+',',convert(varchar(5),[a].[cityId])+',')>0

/*

(2 row(s) affected)

(4 row(s) affected)
id          dm                             cityId
----------- ------------------------------ ------------------------------
1           新疆                             1,2
2           西藏                             2,3,4

(2 row(s) affected)

id          dm                             cityName                       cityId
----------- ------------------------------ ------------------------------ -----------
1           新疆                             乌鲁木齐                           1
1           新疆                             成都                             2
2           西藏                             成都                             2
2           西藏                             包头                             3
2           西藏                             东胜                             4

(5 row(s) affected)


*/