想了良久无解,求SQL大神

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

SELECT A.* FROM ALEFT JOIN BON ',' + 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_aselect 4, '沈阳,长春,厦门' union allselect 5, '长沙,宁波,长春' union allselect 9, '杭州,上海'create table tab_b(sheng varchar(20), shi varchar(20))insert into tab_bselect '福建', '泉州' union allselect '福建', '南平' union allselect '福建', '厦门' union allselect '福建', '三明' union allselect '福建', '龙岩' union allselect '福建', '福州' union allselect '福建', '莆田' union allselect '福建', '漳州' union allselect '福建', '宁德' union allselect '浙江', '宁波' union allselect '浙江', '杭州'-- 测试1,输入'福建'select c.* from tab_a cinner join(select t.id from(select a.id,charindex(b.shi,a.shi) lcfrom tab_a a cross join tab_b b where b.sheng='福建') tgroup by t.id having sum(t.lc)=0) don c.id=d.id/*id          shi----------- --------------------------------------------------5           长沙,宁波,长春9           杭州,上海(2 row(s) affected)*/-- 测试2,输入'浙江'select c.* from tab_a cinner join(select t.id from(select a.id,charindex(b.shi,a.shi) lcfrom tab_a a cross join tab_b b where b.sheng='浙江') tgroup by t.id having sum(t.lc)=0) don c.id=d.id/*id          shi----------- --------------------------------------------------4           沈阳,长春,厦门(1 row(s) affected)*/