表a:
id       shi
4    沈阳,长春,厦门
5    长沙,宁波,长春
9    杭州,上海
表b:
sheng     shi
福建    泉州
福建    南平
福建    厦门
福建    三明
福建    龙岩
福建    福州
福建    莆田
福建    漳州
福建    宁德

SELECT A.* FROM A
LEFT JOIN B
ON ',' + A.SHI + ',' LIKE '%,' + B.SHI + ',%'
AND B.SHENG = '福建'
WHERE B.SHI IS NULL

------解决方案--------------------

SQL code

create table tab_a
(id int, shi varchar(50))

insert into tab_a
select 4, '沈阳,长春,厦门' union all
select 5, '长沙,宁波,长春' union all
select 9, '杭州,上海'

create table tab_b
(sheng varchar(20), shi varchar(20))

insert into tab_b
select '福建', '泉州' union all
select '福建', '南平' union all
select '福建', '厦门' union all
select '福建', '三明' union all
select '福建', '龙岩' union all
select '福建', '福州' union all
select '福建', '莆田' union all
select '福建', '漳州' union all
select '福建', '宁德' union all
select '浙江', '宁波' union all
select '浙江', '杭州'


-- 测试1,输入'福建'
select c.* 
from tab_a c
inner join
(select t.id from
(select a.id,charindex(b.shi,a.shi) lc
from tab_a a cross join tab_b b 
where b.sheng='福建') t
group by t.id having sum(t.lc)=0) d
on c.id=d.id

/*
id          shi
----------- --------------------------------------------------
5           长沙,宁波,长春
9           杭州,上海

(2 row(s) affected)
*/


-- 测试2,输入'浙江'
select c.* 
from tab_a c
inner join
(select t.id from
(select a.id,charindex(b.shi,a.shi) lc
from tab_a a cross join tab_b b 
where b.sheng='浙江') t
group by t.id having sum(t.lc)=0) d
on c.id=d.id

/*
id          shi
----------- --------------------------------------------------
4           沈阳,长春,厦门

(1 row(s) affected)
*/