变量：'103,101,107' 与 表中一个字段 值'101, 102, 103, 107' 进行比较 取字段包含所有变量的记录 单条sql 语句怎么写？ 分数不够再发！！！

[fanshuyi]

变量：'103,101,107' 与 表中一个字段 值'101, 102, 103, 107' 进行比较 取字段包含所有变量的记录 单条sql 语句怎么写？
返回全部包含的记录
SQL语句用程序拼成。

例如:channelid like '%101%' and channelid like '%103%' 不准确
但是LIKE 的出来的结果不准确，希望得到准确值。

[dawugui]

[Quote=引用楼主 fanshuyi 的帖子:]
变量：'103,101,107' 与 表中一个字段 值'101, 102, 103, 107' 进行比较 取字段包含所有变量的记录 单条sql 语句怎么写？
返回全部包含的记录
SQL语句用程序拼成。

例如:channelid like '%101%' and channelid like '%103%' 不准确
但是LIKE 的出来的结果不准确，希望得到准确值。
[/Quote]

变量：'103,101,107'

-------

如果变量最多只有四个值,用PARSENAME可以做到.
如果超过四个,就要使用函数了.

[fanshuyi]

liangCK 数据值 前后 没有“，”会出错的

[fanshuyi]

dawugui 不止4个值 那个值是商品的属性 最多20多个

[liangCK]

哦..我估计看错了..
应该是老乌龟那样.

[dawugui]

--四个以内使用prasename完成.
create table tb(val varchar(50))
insert into tb values('101,102,103,107')
go

declare @val as varchar(50)
set @val = '103,101,107' 

select * from tb where charindex(','+@val+',', ',' + val + ',') > 0 and len(@val) - len(replace(@val,',','')) = 0
union all
select * from tb where charindex(','+parsename(replace(@val,',','.'),2)+',', ',' + val + ',') > 0 and charindex(','+parsename(replace(@val,',','.'),1)+',', ',' + val + ',') > 0 and len(@val) - len(replace(@val,',','')) = 1
union all
select * from tb where charindex(','+parsename(replace(@val,',','.'),3)+',', ',' + val + ',') > 0 and charindex(','+parsename(replace(@val,',','.'),2)+',', ',' + val + ',') > 0 and charindex(','+parsename(replace(@val,',','.'),1)+',', ',' + val + ',') > 0 and len(@val) - len(replace(@val,',','')) = 2
union all
select * from tb where charindex(','+parsename(replace(@val,',','.'),4)+',', ',' + val + ',') > 0 and charindex(','+parsename(replace(@val,',','.'),3)+',', ',' + val + ',') > 0 and charindex(','+parsename(replace(@val,',','.'),2)+',', ',' + val + ',') > 0 and charindex(','+parsename(replace(@val,',','.'),1)+',', ',' + val + ',') > 0 and len(@val) - len(replace(@val,',','')) = 3

drop table tb

/*
val                                                
-------------------------------------------------- 
101,102,103,107

（所影响的行数为 1 行）
*/

[fanshuyi]

真的不止4个 像上面那也写 我估计 会疯掉的 呵呵

[-狙击手-]

还不好搞成函数，

省心

[fanshuyi]

帮忙看看呀 分数不够再加 再加 再加……

[沃尔特容易]

--这个是我写的函数，经过验证，是好用的，我在项目中就是用的这个，判断

--两个以逗号分割的字符串的共有的部分

--正好和你的需求一致


CREATE FUNCTION StringANDString
( 
@a nvarchar(2048), 
@b nvarchar(2048) 
) 
RETURNS nvarchar(2048)
AS
begin
declare @aa nvarchar(2048)
declare @bb nvarchar(2048)
if(@a is null)
set @a = N''
if(@b is null)
set @b = N''
start_aclearspace_loop:
if(charindex(N', ',@a)=0 and charindex(N' ,',@a)=0 and charindex(N',,',@a)=0)
begin
set @a = N',,' + @a + N',,';
set @a = LTRIM(RTRIM(replace(replace(@a,N',,,',N''),N',,',N'')))
goto end_aclearspace_loop;
end
else
begin
set @a = LTRIM(RTRIM(replace(replace(replace(@a,N', ',N','),N' ,',N','),N',,',N',')))
goto start_aclearspace_loop
end
end_aclearspace_loop:
start_bclearspace_loop:
if(charindex(N', ',@b)=0 and charindex(N' ,',@b)=0 and charindex(N',,',@b)=0)
begin
set @b = N',,' + @b + N',,';
set @b = LTRIM(RTRIM(replace(replace(@b,N',,,',N''),N',,',N'')))
goto end_bclearspace_loop;
end
else
begin
set @b = LTRIM(RTRIM(replace(replace(replace(@b,N', ',N','),N' ,',N','),N',,',N',')))
goto start_bclearspace_loop
end
end_bclearspace_loop:
set @aa = @a
set @bb = @b
if(@aa is null)
set @aa = N'';
set @aa = LTRIM(RTRIM(@aa))
if(@bb is null)
set @bb = N'';
set @bb = LTRIM(RTRIM(@bb))
if(@aa='' or @bb='')
return ''
declare @cc nvarchar(2048)
set @cc = @aa + N',' + @bb
declare @c nvarchar(2048)
set @c = N''
start_loop:
set @cc = LTRIM(RTRIM(@cc))
set @c = LTRIM(RTRIM(@c))
declare @cci int;
set @cci = charindex(',',@cc)
if @cc is not null and @cc <>''
begin
if @cci=0
begin
if(charindex(N','+@cc+N',',N','+@c+N',')=0 and charindex(N','+@cc+N',',N','+@aa+N',')<>0 and charindex(N','+@cc+N',',N','+@bb+N',')<>0)
begin
if(@c<>N'')
set @c = @c + N','
set @c = @c + @cc
end
goto end_loop
end
if @cci=1
begin
set @cc = substring(@cc,@cci+1,len(@cc)-1);
goto start_loop
end
else
begin
if(substring(@cc,1,charindex(',',@cc)-1) is not null and LTRIM(RTRIM(substring(@cc,1,charindex(',',@cc)-1)))<>N'' and charindex(N','+LTRIM(RTRIM(substring(@cc,1,charindex(',',@cc)-1)))+N',',N','+@c+N',')=0 and charindex(N','+LTRIM(RTRIM(substring(@cc,1,charindex(',',@cc)-1)))+N',',N','+@aa+N',')<>0 and charindex(N','+LTRIM(RTRIM(substring(@cc,1,charindex(',',@cc)-1)))+N',',N','+@bb+N',')<>0)
begin
if(@c is not null and @c<>N'')
set @c = @c + ',';
set @c = @c + RTRIM(LTRIM(substring(@cc,1,charindex(',',@cc)-1)))
end 
set @cc = substring(@cc,charindex(',',@cc),len(@cc)-charindex(',',@cc)+1)
goto start_loop
end
end
end_loop:
return @c
end

[沃尔特容易]

使用时,dbo.StringANDString('1,2,3,a,b,c','11,2,123,a,a,a,a,a,c')
返回结果是
'2,a'

[wzy_love_sly]

顶顶 接点分 我是真不会

[沃尔特容易]

返回结果是'2,a,c',少写了一个c

[fanshuyi]

我要单条SQL！

[dawugui]

--sql server 2000使用临时表解决
create table tb(id int , [values] varchar(50))
insert into tb values(1,'101,102,103,107')
insert into tb values(2,'101,108,103,107')
insert into tb values(3,'101,108,102,107')
go
--建立一临时表tmp1.
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO tmp FROM syscolumns a, syscolumns b 
--设置变量，并将变量放入另一临时表tmp2。
declare @sql as varchar(1000)
declare @str as varchar(100)
set @str = '103,101,107' 
set @sql= 'select '+replace(@str,',',' as id union all select ')
exec('select * into tmp2 from (' + @sql + ') t')

select m.* from tb m where id in
(
  select distinct id from
  (
    SELECT A.id, [values] = SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
    FROM tb A, tmp B
    WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','
  ) t1 
  where [values] in (select id from tmp2)
  group by id 
  having count(*) = (select count(*) from tmp2)
)

drop table tb,tmp,tmp2

/*
id          values                                             
----------- -------------------------------------------------- 
1           101,102,103,107
2           101,108,103,107
（所影响的行数为 2 行）
*/

[dawugui]

[Quote=引用 16 楼 fanshuyi 的回复:]
我要单条SQL！
[/Quote]
单条SQL解决不了.

[fanshuyi]

dawugui 那我如何修改结构 才能单条语句实现吗 现有的搜索程序是在不想改了 http://search.ebyshow.com

[fanshuyi]

决定了 重新设计数据库

[wzy_love_sly]

... 结帖给分吧蔚蓝

[-狙击手-]

单条搞不定

[dobear_0922]

一句搞定，不过必须用SQL２００５才行

create table tb(id int , [values] varchar(50))
insert into tb values(1,'101,102,103,107')
insert into tb values(2,'101,108,103,107')
insert into tb values(3,'101,108,102,107')

declare @nums xml
set @nums = '<num v="'+replace('103,101,107', ',', '"></num><num v="')+'"></num>' 


select * from tb
where not exists
(
	select num from (select num=x.value('@v', 'varchar(8)') from @nums.nodes('/num') t(x)) T
	where charindex(','+num+',', ','+tb.[values]+',')=0
)

/*
id          values
----------- --------------------------------------------------
1           101,102,103,107
2           101,108,103,107

(2 row(s) affected)
*/

drop table tb