表1结构如下question1 idd name
A.盈利,B.微利,D.微亏, 1 a1
A.盈利,C.持平, 1 b1
A.盈利,B.微利, 1 c1
E.亏损, 1 d1
A.盈利,C.持平, 1 e1
C.持平, 1 f1
A.盈利,D.微利, 1 g1
A.个人选择,B.朋友指导,C.专家建议, 2 a1
A.个人选择,C.专家建议, 2 b1
C.专家建议, 2 c1
D.券商推荐-券商名称, 2 e1
B.朋友指导,C.专家建议, 2 f1
A.个人选择,C.专家建议, 2 g1
B.朋友指导,D.券商推荐-券商名称, 2 h1先在我想通过一个字符 "1A.盈利,D.微利,#2A.个人选择,C.专家建议," 查询出
a1
g1
(解释:其实是用#号隔开的两条记录,每条记录的第一个字符就是idd,我的目的就是想只要是idd=1里包含有“A.盈利,D.微利”这两个答案并且idd=2包含有"A.个人选择,C.专家建议"这两个答案的人找出来)用储存过程如何实现!麻烦各位大哥帮看看,小弟万分感谢!
A.盈利,B.微利,D.微亏, 1 a1
A.盈利,C.持平, 1 b1
A.盈利,B.微利, 1 c1
E.亏损, 1 d1
A.盈利,C.持平, 1 e1
C.持平, 1 f1
A.盈利,D.微利, 1 g1
A.个人选择,B.朋友指导,C.专家建议, 2 a1
A.个人选择,C.专家建议, 2 b1
C.专家建议, 2 c1
D.券商推荐-券商名称, 2 e1
B.朋友指导,C.专家建议, 2 f1
A.个人选择,C.专家建议, 2 g1
B.朋友指导,D.券商推荐-券商名称, 2 h1先在我想通过一个字符 "1A.盈利,D.微利,#2A.个人选择,C.专家建议," 查询出
a1
g1
(解释:其实是用#号隔开的两条记录,每条记录的第一个字符就是idd,我的目的就是想只要是idd=1里包含有“A.盈利,D.微利”这两个答案并且idd=2包含有"A.个人选择,C.专家建议"这两个答案的人找出来)用储存过程如何实现!麻烦各位大哥帮看看,小弟万分感谢!
insert into tb select 'A.盈利,B.微利,D.微亏,','1','a1'
insert into tb select 'A.盈利,C.持平,','1','b1'
insert into tb select 'A.盈利,B.微利,','1','c1'
insert into tb select 'E.亏损,','1','d1'
insert into tb select 'A.盈利,C.持平,','1','e1'
insert into tb select 'C.持平,','1','f1'
insert into tb select 'A.盈利,D.微利,','1','g1'
insert into tb select 'A.个人选择,B.朋友指导,C.专家建议,','2','a1'
insert into tb select 'A.个人选择,C.专家建议,','2','b1'
insert into tb select 'C.专家建议,','2','c1'
insert into tb select 'D.券商推荐-券商名称,','2','e1'
insert into tb select 'B.朋友指导,C.专家建议,','2','f1'
insert into tb select 'A.个人选择,C.专家建议,','2','g1'
insert into tb select 'B.朋友指导,D.券商推荐-券商名称,','2','h1'
go
create procedure getname
(@str nvarchar(30))
as
select distinct name
from tb
where question1=stuff(LEFT(@str,charindex('#',@str)-1),1,1,'')
or question1=stuff(RIGHT(@str,LEN(@str)-charindex('#',@str)),1,1,'')
go
exec getname '1A.盈利,D.微利,#2A.个人选择,C.专家建议,'/*
name
----------
b1
g1(2 行受影响)*/
go
drop table tb
drop procedure getname
----创建测试表
CREATE TABLE T
(
question1 varchar(50),
idd int,
name varchar(20)
)
--插入测试数据
INSERT INTO T
SELECT 'A.盈利,B.微利,D.微亏',1,'a1' UNION ALL
SELECT 'A.盈利,C.持平',1,'b1' UNION ALL
SELECT 'A.盈利,B.微利', 1 ,'c1' UNION ALL
SELECT 'E.亏损', 1,'d1' UNION ALL
SELECT 'A.盈利,C.持平',1,'e1' UNION ALL
SELECT 'C.持平', 1 ,'f1' UNION ALL
SELECT 'A.盈利,D.微利', 1, 'g1' UNION ALL
SELECT 'A.个人选择,B.朋友指导,C.专家建议', 2 ,'a1' UNION ALL
SELECT 'A.个人选择,C.专家建议', 2 ,'b1' UNION ALL
SELECT 'C.专家建议', 2 ,'c1' UNION ALL
SELECT 'D.券商推荐-券商名称', 2, 'e1' UNION ALL
SELECT 'B.朋友指导,C.专家建议', 2 ,'f1' UNION ALL
SELECT 'A.个人选择,C.专家建议', 2 ,'g1' UNION ALL
SELECT 'B.朋友指导,D.券商推荐-券商名称', 2, 'h1'GO --存储过程
CREATE PROC QUERY_TEST
@P1 varchar(4000)=NULL
AS
set @P1=ISNULL(@P1,'') IF @P1<>''
BEGIN
CREATE TABLE #TMP
(
ID int,
DSC varchar(4000)
) DECLARE @Ltmp varchar(4000)
--拆分查询条件
WHILE CHARINDEX('#',@P1)>0
BEGIN
SET @Ltmp = SUBSTRING(@P1,1,CHARINDEX('#',@P1)-1)
if LEN(@Ltmp)>0
INSERT INTO #TMP SELECT SUBSTRING(@Ltmp,1,1),SUBSTRING(@Ltmp,2,len(@Ltmp)-1) SET @P1=SUBSTRING(@P1,CHARINDEX('#',@P1)+1,LEN(@P1)-CHARINDEX('#',@P1))
END
IF LEN(@P1)>0
BEGIN
INSERT INTO #TMP
SELECT SUBSTRING(@P1,1,1),SUBSTRING(@P1,2,len(@P1)-1)
END --查询结果
SELECT DISTINCT B.name FROM #TMP A INNER JOIN T B ON A.ID=B.idd AND CHARINDEX(A.DSC,B.question1)>0
END
GO
--测试
QUERY_TEST '1A.盈利,B.微利,#2A.个人选择,C.专家建议'DROP TABLE T
DROP PROC QUERY_TEST--执行结果
/*
(1 行受影响)
name
--------------------
a1
b1
g1
*/
insert into tb select 'A.盈利,B.微亏,D.微利,','1','a1'现在的问题是比如我要查询包含1A.盈利,D.微利答案的记录这个时候应该查询出'A.盈利,B.微亏,D.微利,'这条记录的,但是查不出,只有连续的字符串才能查得出例如'A.盈利,B.微利'麻烦qianjin036a大哥再帮看看,小弟万分感谢
insert into tb select 'A.盈利,B.微利,D.微亏,','1','a1'
insert into tb select 'A.盈利,C.持平,','1','b1'
insert into tb select 'A.盈利,B.微利,','1','c1'
insert into tb select 'E.亏损,','1','d1'
insert into tb select 'A.盈利,C.持平,','1','e1'
insert into tb select 'C.持平,','1','f1'
insert into tb select 'A.盈利,D.微利,','1','g1'
insert into tb select 'A.个人选择,B.朋友指导,C.专家建议,','2','a1'
insert into tb select 'A.个人选择,C.专家建议,','2','b1'
insert into tb select 'C.专家建议,','2','c1'
insert into tb select 'D.券商推荐-券商名称,','2','e1'
insert into tb select 'B.朋友指导,C.专家建议,','2','f1'
insert into tb select 'A.个人选择,C.专家建议,','2','g1'
insert into tb select 'B.朋友指导,D.券商推荐-券商名称,','2','h1'
go
create procedure getname
(@str nvarchar(30))
as
begin
declare @str1 nvarchar(50),@str2 nvarchar(50)
set @str1=stuff(LEFT(@str,charindex('#',@str)-1),1,1,'')
set @str2=stuff(RIGHT(@str,LEN(@str)-charindex('#',@str)),1,1,'')
;with cte as(
select 1 as flg,substring(@str1,number+1,charindex(',',@str1,number+1)-number) as s from master..spt_values
where type='p' and number<=len(@str1) and substring(@str1,number,1)<>',' and substring(','+@str1,number,1)=','
union all
select 2 as flg,substring(@str2,number+1,charindex(',',@str2,number+1)-number) as s from master..spt_values
where type='p' and number<=len(@str2) and substring(@str2,number,1)<>',' and substring(','+@str2,number,1)=','
),cte1 as(
select * from tb a inner join cte b on charindex(b.s,a.question1)>0
)
select question1,idd,name from cte1 a group by question1,idd,name,flg having count(*)=
(select COUNT(*) from cte where flg=a.flg)
end
go
exec getname '1A.盈利,D.微利,#2A.个人选择,C.专家建议,'
/*
question1 idd name
------------------------------ ----------- ----------
A.盈利,B.微利, 1 c1
A.盈利,B.微利,D.微亏, 1 a1
A.盈利,D.微利, 1 g1
A.个人选择,B.朋友指导,C.专家建议, 2 a1
A.个人选择,C.专家建议, 2 b1
A.个人选择,C.专家建议, 2 g1(6 行受影响)
*/
go
drop table tb
drop procedure getname
create table tb(question1 nvarchar(30),idd int,name varchar(10))
insert into tb select 'A.盈利,B.微利,D.微亏,','1','a1'
insert into tb select 'A.盈利,C.持平,','1','b1'
insert into tb select 'A.盈利,B.微利,','1','c1'
insert into tb select 'E.亏损,','1','d1'
insert into tb select 'A.盈利,C.持平,','1','e1'
insert into tb select 'C.持平,','1','f1'
insert into tb select 'A.盈利,D.微利,','1','g1'
insert into tb select 'A.个人选择,B.朋友指导,C.专家建议,','2','a1'
insert into tb select 'A.个人选择,C.专家建议,','2','b1'
insert into tb select 'C.专家建议,','2','c1'
insert into tb select 'D.券商推荐-券商名称,','2','e1'
insert into tb select 'B.朋友指导,C.专家建议,','2','f1'
insert into tb select 'A.个人选择,C.专家建议,','2','g1'
insert into tb select 'B.朋友指导,D.券商推荐-券商名称,','2','h1'
go
create procedure getname
(@str nvarchar(30))
as
begin
declare @str1 nvarchar(50),@str2 nvarchar(50)
set @str1=stuff(LEFT(@str,charindex('#',@str)-1),1,1,'')
set @str2=stuff(RIGHT(@str,LEN(@str)-charindex('#',@str)),1,1,'')
;with cte as(
select 1 as flg,substring(@str1,number,charindex(',',@str1,number+1)-number+1) as s from master..spt_values
where type='p' and number<=len(@str1) and substring(@str1,number,1)<>',' and substring(','+@str1,number,1)=','
union all
select 2 as flg,substring(@str2,number,charindex(',',@str2,number+1)-number+1) as s from master..spt_values
where type='p' and number<=len(@str2) and substring(@str2,number,1)<>',' and substring(','+@str2,number,1)=','
),cte1 as(
select * from tb a inner join cte b on charindex(b.s,a.question1)>0
)
select question1,idd,name from cte1 a group by question1,idd,name,flg having count(*)=
(select COUNT(*) from cte where flg=a.flg)
end
go
exec getname '1A.盈利,D.微利,#2A.个人选择,C.专家建议,'
/*
question1 idd name
------------------------------ ----------- ----------
A.盈利,D.微利, 1 g1
A.个人选择,B.朋友指导,C.专家建议, 2 a1
A.个人选择,C.专家建议, 2 b1
A.个人选择,C.专家建议, 2 g1(4 行受影响)
*/
go
drop table tb
drop procedure getname
CSDN好象中断了一会儿,
将存储过程稍改一下就OK,看下是不是你要的效果? 我用的方法可能比较笨。
我的思路是先将参数拆分成 idd,和question两个字段, 再将 question中间的逗号换成 %, 这样就可以模糊查询实现你的需求--存储过程
ALTER PROC QUERY_TEST
@P1 varchar(4000)=NULL
AS
set @P1=ISNULL(@P1,'') IF @P1<>''
BEGIN
CREATE TABLE #TMP
(
ID int,
DSC varchar(4000)
) DECLARE @Ltmp varchar(4000)
--拆分查询条件
WHILE CHARINDEX('#',@P1)>0
BEGIN
SET @Ltmp = SUBSTRING(@P1,1,CHARINDEX('#',@P1)-1)
if LEN(@Ltmp)>0
INSERT INTO #TMP SELECT SUBSTRING(@Ltmp,1,1),'%'+REPLACE(SUBSTRING(@Ltmp,2,len(@Ltmp)-1),',','%')+'%' SET @P1=SUBSTRING(@P1,CHARINDEX('#',@P1)+1,LEN(@P1)-CHARINDEX('#',@P1))
END
IF LEN(@P1)>0
BEGIN
INSERT INTO #TMP
SELECT SUBSTRING(@P1,1,1),'%'+REPLACE(SUBSTRING(@P1,2,len(@P1)-1),',','%')+'%'
END --查询结果
SELECT DISTINCT B.name FROM #TMP A INNER JOIN T B ON A.ID=B.idd AND question1 LIKE DSC
END
GO