sql split查询,该怎么处理

sql split查询
wb
ID QuestionClassification
a 1,2,3,4,5,6,7
b 11,22,33,44,55,66,77
c 111,222,333,444,555,666,777

wu
id name KeyWord
1 aa 1,22,333
2 bb 111,1,22
3 cc 22,33


select QuestionClassification from wb where ID='a'
我从wb中查到的数据 1,2,3,4,5,6,7


怎么才能通过我查到的1,2,3,4,5,6,7(以,格开进行查询)

因为id 1和2 的KeyWord包含 1,2,3,4,5,6,7中的1
所以怎么从表 wu 中查出
1 aa 1,22,333
2 bb 111,1,22


[解决办法]

SQL code

if object_id('[wb]') is not null drop table [wb]gocreate table [wb]([ID] varchar(1),[QuestionClassification] varchar(27))insert [wb]select 'a','1,2,3,4,5,6,7' union allselect 'b','11,22,33,44,55,66,77' union allselect 'c','111,222,333,444,555,666,777'goif object_id('[wu]') is not null drop table [wu]gocreate table [wu]([id] int,[name] varchar(2),[KeyWord] varchar(8))insert [wu]select 1,'aa','1,22,333' union allselect 2,'bb','111,1,22' union allselect 3,'cc','22,33'goselect a.* from wu ajoin(select substring(QuestionClassification,b.number,charindex(',',QuestionClassification+',',b.number)-b.number) as num from wb ajoin master..spt_values b on b.type='P'  and charindex(',',','+QuestionClassification,b.number)=b.numberwhere ID='a') bon charindex(','+b.num+',',','+a.keyword+',')>0/**id          name KeyWord----------- ---- --------1           aa   1,22,3332           bb   111,1,22(2 行受影响)**/
[解决办法]
SQL code
/*create function [dbo].[m_split](@c varchar(2000),@split varchar(2))       returns @t table(col varchar(200))       as         begin           while(charindex(@split,@c) <>0)             begin               insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))                 set @c = stuff(@c,1,charindex(@split,@c),'')           end           insert @t(col) values (@c)           return     end */declare @wb table([ID] varchar(1),[QuestionClassification] varchar(27))insert @wbselect 'a','1,2,3,4,5,6,7' union allselect 'b','11,22,33,44,55,66,77' union allselect 'c','111,222,333,444,555,666,777'declare @wu table([id] int,[name] varchar(2),[KeyWord] varchar(8))insert @wuselect 1,'aa','1,22,333' union allselect 2,'bb','111,1,22' union allselect 3,'cc','22,33';with maco as(select * from [dbo].[m_split]((select [QuestionClassification] from @wb where ID='a'),','))select * from @wu a ,maco b where charindex(','+ltrim(b.col)+',',','+a.[KeyWord]+',')>0/*id          name KeyWord  col----------- ---- -------- ------------------------------1           aa   1,22,333 12           bb   111,1,22 1*/
[解决办法]
探讨


SQL code

/*
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))    
   returns @t table(col varchar(200))    
   as    
     begin    
       while(charindex(@split,@c) <>0)    
   ……