create proc [dbo].[SelectID]
@where varchar(max),
@count int as
declare @sql varchar(max)
set @sql='select FK_Iid from F_Commodity where Brand_name in('+@where+') group by FK_Iid having count(distinct Brand_name) >='+LTRIM(@count)+''
EXEC (@sql)这个存储过程是对的
但是我不知道怎么转化where 参数 传送过来的where是这样的 '潘婷','飘柔'但是这个样子不对哦 数据库有记录 但是返回的是0正常的SQL是: select FK_Iid from F_Commodity where Brand_name in('飘柔','潘婷') group by FK_Iid having count(distinct Brand_name) >=LTRIM(2)不知道怎么转划了。 等待高手!!
set @s='''潘婷'',''飘柔'''
@where varchar(max),
@count int as
begin
declare @sql varchar(max)
set @sql='select FK_Iid from F_Commodity where charindex(Brand_name,'''+@where+''') > 0 group by FK_Iid having count(distinct Brand_name) >='+LTRIM(@count)+'
EXEC (@sql)
end
@where varchar(max),
@count int as
begin
declare @sql varchar(max)
set @sql='select FK_Iid from F_Commodity where charindex(Brand_name,'''+@where+''') > 0 group by FK_Iid having count(distinct Brand_name) >='+LTRIM(@count)
EXEC (@sql)
end
@where varchar(max),
@count int
as
select FK_Iid
from F_Commodity
where charindex(','+Brand_name+',',','+@where+',')>0
group by FK_Iid
having count(distinct Brand_name) >=@countGO--调用:exec [dbo].[SelectID] '潘婷,飘柔',10
@where varchar(max),
@count int as
begin
select FK_Iid from F_Commodity
where charindex(',' + Brand_name + ',' , ',' + @where + ',') > 0
group by FK_Iid having count(distinct Brand_name) >= @count
endcreate proc [dbo].[SelectID]
@where varchar(max),
@count int as
begin
select FK_Iid from F_Commodity
where ',' + @where + ',' like '%,' + Brand_name + ',%'
group by FK_Iid having count(distinct Brand_name) >= @count
end
@starte varchar(10),
@where varchar(max),
@count int
as
declare @sql varchar(max)
if (@starte='or')
begin
set @sql='select distinct FK_Iid from F_Commodity where Brand_name in ('''+@where +''')'
end
else
begin
set @sql= 'select FK_Iid from F_Commodity
where charindex('','' +Brand_name+'','','','''+@where+''','')>0
group by FK_Iid
having count(distinct Brand_name) >=@count '
end
EXEC (@sql)exec [dbo].[SelectID] 'and', '沙宣,飘柔',2 我这个按照你的来错误了
@starte varchar(10),
@where varchar(max),
@count int
as
if (@starte='or')
select distinct FK_Iid from F_Commodity where charindex(',' +Brand_name+',',','+@where+',')>0
else
select FK_Iid from F_Commodity
where charindex(',' +Brand_name+',',','+@where+',')>0
group by FK_Iid
having count(distinct Brand_name) >=@count GOexec [dbo].[SelectID] 'and', '沙宣,飘柔',2 你这样试试,别再画蛇添足了。
insert into F_Commodity values(1,'沙宣')
insert into F_Commodity values(2,'飘柔')
insert into F_Commodity values(1,'沙宣')
insert into F_Commodity values(1,'沙宣')
insert into F_Commodity values(2,'飘柔')
gocreate procedure myproc @where varchar(100),@count int
as
begin
select FK_Iid from F_Commodity
where charindex(',' + Brand_name + ',' , ',' + @where + ',') > 0
group by FK_Iid
having count(1) >= @count
end
goexec myproc '沙宣,飘柔' , 2drop proc myprocdrop table F_Commodity/*
FK_Iid
-----------
1
2(所影响的行数为 2 行)
*/