要求根据所选的抽题条件过滤试题试卷表:Table1Code(编号) IPCode(所属题库) DiffLevel(题目难度1-9个系数) ItemType(题目类型0-3) KnowledgePoints(知识点编号,可以有多个知识点以“|”分开,如:k2|k3|k4) 1 IP001 2 1 K1|K2|K3
2 IP001 7 3 K1
3 IP002 4 3 K1|K3
4 IP001 5 2 K1|K2现在要根据 IPCode,DiffLevel,ItemType,KnowledgePoints 四个条件查询试题,
在SQL2000中,查询语句怎么写
2 IP001 7 3 K1
3 IP002 4 3 K1|K3
4 IP001 5 2 K1|K2现在要根据 IPCode,DiffLevel,ItemType,KnowledgePoints 四个条件查询试题,
在SQL2000中,查询语句怎么写
这样写不行. 如果 KnowledgePoints 这个条件没输,
sql最后面会成这样,是会查不到数据的. AND KnowledgePoints='';
因为SQL2000、SQL2005中,查询有个问题,例如说字段“abc”
我可以用like 和in 模糊查询到包涵"a","ab","abc","bc"的字段,却不能查询包涵"ac"的字段,
and KnowledgePoints like '%'+@KnowledgePoints+'%'
declare @test varchar(20) set @test ='K1|K2|K3' select CHARINDEX('K3'+'|',@test+'|')类似这样判断,只要CHARINDEX>0即可
按照#5的方法,我只能从数据库中查到
"K2|K4"
.........
却查询不到
"K2|K3|K4"
declare @t table(Code int ,IPCode varchar(20), DiffLevel int , ItemType int, KnowledgePoints varchar(20)) insert into @t values(1,'IP001',2,1,'K1|K2|K3' )
insert into @t values(2,'IP001',7,3,'K1' )
insert into @t values(3,'IP002',4,3,'K1|K3' )
insert into @t values(4,'IP001',5,2,'K1|K2' )
insert into @t values(4,'IP001',5,2,'K1|K4|K2' )
select * from @t where IPCode='IP001' and ( charindex('K1'+'|',KnowledgePoints+'|')>0 and charindex('K2'+'|',KnowledgePoints+'|')>0)
把你的条件K1|K2拆分成
charindex('K1'+'|',KnowledgePoints+'|')>0 and charindex('K2'+'|',KnowledgePoints+'|')>0
该变量里存的字符串是以逗号隔开的,比如:"K1|K2|K3,K1|K3,K1|K2";
偶遇到过,但没这样做,这样不安全,容易注入,建议用存储过程.
但如果在不知道KnowledgePoints 的值的情况下,怎么弄,能说下吗?
13楼用In好像不太行,可以考虑用存储过程,谢谢各位的帮忙!