表一
id s1 s2 s3 s4 s5
1 1 2 3 4 5
2 1 0 0 0 0表二id s
1 1,2,5求得表一中s1,s2,s3,s4,s5列包含表二中s列的记录.
结果是
id s1 s2 s3 s4 s5
1 1 2 3 4 5
id s1 s2 s3 s4 s5
1 1 2 3 4 5
2 1 0 0 0 0表二id s
1 1,2,5求得表一中s1,s2,s3,s4,s5列包含表二中s列的记录.
结果是
id s1 s2 s3 s4 s5
1 1 2 3 4 5
INSERT INTO ta
SELECT 1, 1, 2, 3, 4, 5
UNION ALL SELECT 2, 1, 0, 0, 0, 0CREATE TABLE tb([id] int, [s] VARCHAR(10))
INSERT INTO tb
SELECT 1, '1,2,5'SELECT A.* FROM ta A, tb B
WHERE (',' + CAST(S1 AS VARCHAR) + ',,'+ CAST(S2 AS VARCHAR)+ ',,' + CAST(S3 AS VARCHAR)
+ ',,' + CAST(S4 AS VARCHAR)+ ',,' + CAST(S5 AS VARCHAR) +',')
LIKE(',%' + REPLACE(B.S, ',', ',%,') + ',%')DROP TABLE TA, TB
id int,
s1 int,
s2 int,
s3 int,
s4 int,
s5 int
)
insert @表一 select
1, 1, 2, 3, 4, 5
union all select
2, 1, 0, 0, 0, 0
declare @表二 table(
id int,
s varchar(20)
)insert @表二 select
1, '1,2,5'select b.*
from (
select id,s1 as s from @表一
union all
select id,s2 as s from @表一
union all
select id,s3 as s from @表一
union all
select id,s4 as s from @表一
union all
select id,s5 as s from @表一
) as t,@表二 a,@表一 b
where ','+a.s+',' like '%,'+cast(t.s as varchar)+',%'
and t.id=b.id
group by t.id,a.id,a.s,b.id,b.s1,b.s2,b.s3,b.s4,b.s5
having count(*)=len(a.s)-len(replace(a.s,',',''))+1--结果
id s1 s2 s3 s4 s5
----------- ----------- ----------- ----------- ----------- -----------
1 1 2 3 4 5(所影响的行数为 1 行)
1 5 2 3 4 1而要求查
1,2,5就查不到
其实,最简便的方法是用函数,不过函数的方法不好推荐,效率太成问题
设计上应没什么问题.s1 s2 s3 s4 s5是一人身上各个位置的装备列.表二中s列是修练某项技能需要装备的东西..