sql 如何实现这样的功能？

--参考:
/*
标题：分解字符串并查询相关数据
作者：爱新觉罗.毓华
时间：2008-03-18
地点：广东深圳
说明：通过使用函数等方法分解字符串查询相关数据。问题：通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。
例如 @str = '1,2,3',查询下表得到记录1,4,5,6
ID TypeID
1  1,2,3,4,5,6,7,8,9,10,11,12
2  2,3
3  3,7,8,9
4  2,6
5  4,5
6  6,7
*/
-----------------------------
create table tb (ID int , TypeID varchar(30))
insert into tb values(1 , '1,2,3,4,5,6,7,8,9,10,11,12')
insert into tb values(2 , '2,3')
insert into tb values(3 , '3,7,8,9')
insert into tb values(4 , '2,6')
insert into tb values(5 , '4,5')
insert into tb values(6 , '6,7')
go
-----------------------------
--如果仅仅是一个,如@str = '1'.
declare @str as varchar(30)
set @str = '1'
select * from tb where charindex(',' + @str + ',' , ',' + TypeID + ',') > 0
select * from tb where ',' + TypeID + ',' like '%,' + @str + ',%'
/*
ID          TypeID
----------- ------------------------------
1           1,2,3,4,5,6,7,8,9,10,11,12
（所影响的行数为 1 行）
*/-----------------------------
--如果包含两个,如@str = '1,2'.
declare @str as varchar(30)
set @str = '1,2'
select * from tb where charindex(',' + left(@str , charindex(',' , @str) - 1) + ',' , ',' + typeid + ',') > 0 or
  charindex(',' + substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',' , ',' + typeid + ',') > 0
select * from tb where ',' + typeid + ',' like '%,' + left(@str , charindex(',' , @str) - 1) + ',%' or
  ',' + typeid + ',' like '%,' + substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',%'
/*
ID          TypeID
----------- ------------------------------
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
4           2,6
（所影响的行数为 3 行）
*/-------------------------------------------
--如果包含三个或四个，用PARSENAME函数来处理.
declare @str as varchar(30)
set @str = '1,2,3,4'
select * from tb where
  charindex(',' + parsename(replace(@str , ',' , '.') , 4) + ',' , ',' + typeid + ',') > 0 or
  charindex(',' + parsename(replace(@str , ',' , '.') , 3) + ',' , ',' + typeid + ',') > 0 or
  charindex(',' + parsename(replace(@str , ',' , '.') , 2) + ',' , ',' + typeid + ',') > 0 or
  charindex(',' + parsename(replace(@str , ',' , '.') , 1) + ',' , ',' + typeid + ',') > 0
select * from tb where
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 4) + ',%' or
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 3) + ',%' or
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 2) + ',%' or
  ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 1) + ',%'
/*
ID          TypeID
----------- ------------------------------
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
3           3,7,8,9
4           2,6
5           4,5
（所影响的行数为 5 行）
*/---------------------------------------
--如果超过四个，则只能使用函数或动态SQL来分解并查询数据。
/*
名称：fn_split函数.
功能：实现字符串分隔功能的函数
*/
create function dbo.fn_split(@inputstr varchar(8000), @seprator varchar(10))
returns @temp table (a varchar(200))
as
begin
  declare @i int
  set @inputstr = rtrim(ltrim(@inputstr))
  set @i = charindex(@seprator , @inputstr)
  while @i >= 1
  begin
    insert @temp values(left(@inputstr , @i - 1))
    set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
    set @i = charindex(@seprator , @inputstr)
  end
  if @inputstr <> '\'
  insert @temp values(@inputstr)
  return
end
go--调用
declare @str as varchar(30)
set @str = '1,2,3,4,5'select distinct m.* from tb m,
(select * from dbo.fn_split(@str,',')) n
where charindex(',' + n.a + ',' , ',' + m.typeid + ',') > 0drop table tb
drop function dbo.fn_split /*
ID          TypeID
----------- ------------------------------
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
3           3,7,8,9
4           2,6
5           4,5
（所影响的行数为 5 行）
*/------------------------------------------
--使用动态SQL的语句。
declare @str varchar(200)
declare @sql as varchar(1000)
set @str = '1,2,3,4,5'
set @sql = 'select ''' + replace(@str , ',' , ''' as id union all select ''')
set @sql = @sql + ''''
set @sql = 'select distinct a.* from tb a , (' + @sql + ') b where charindex(' + ''','' + b.id + ' + ''',''' + ' , ' + ''','' + a.typeid + ' + ''',''' + ') > 0 '
exec (@sql)
/*
ID          TypeID
----------- ------------------------------
1           1,2,3,4,5,6,7,8,9,10,11,12
2           2,3
3           3,7,8,9
4           2,6
5           4,5
（所影响的行数为 5 行）
*/

纠正下错误，问题重新描述如下：
比如我有两个表：
table 1
col1   col2
a      2,3,5
b      5,3
table 2
col3   col4
aa     1,5,8
bb     2,1,4
cc     3,4,6
通过第一个表中指定行的col2字段的被逗号分开的值去查询table2 col4
如果有个相等，这这条就符合查询要求。
用table1的第一行查询结果是：
col3   col4
aa     1,5,8
bb     2,1,4
cc     3,4,6 用table1的第二行查询结果是：
col3   col4
aa     1,5,8
cc     3,4,6
存储过程怎么写？

第一行 col2 = 2,3,5 在表tb2中aa包含5,bb包含2,cc包含3,所以三条记录都是.
第一行 col2 = 5,3 在表tb2中aa包含5,cc包含3,所以结果是aa,cc.

第一行 col2 = 2,3,5 在表tb2中aa包含5,bb包含2,cc包含3,所以三条记录都是.
第二行 col2 = 5,3 在表tb2中aa包含5,cc包含3,所以结果是aa,cc.

create table tb1(col1 varchar(10),col2 varchar(10))
insert into tb1 select 'a'   ,   '2,3,5'
insert into tb1 select 'b'   ,   '5,3'  create table tb2(col3 varchar(10),col4 varchar(10))
insert into tb2 select 'aa'  ,   '1,5,8'
insert into tb2 select 'bb'  ,   '2,1,4'
insert into tb2 select 'cc'  ,   '3,4,6' select top 10 id=identity(int,1,1) into # from syscolumns select b.col3,b.col4
from
(
   select col1,substring(col2,id,charindex(',',col2+',',id)-id) col2
   from tb1 a join # b on substring(','+col2,id,1)=','
) a join tb2 b
  on charindex(','+a.col2+',',','+b.col4+',')>0
where a.col1='b' --a.col1='a'
order by col3drop table tb1,tb2,#

--> 测试数据: #1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1 (col1 varchar(1),col2 varchar(5))
insert into #1
select 'a','2,3,5' union all
select 'b','5,3'
--> 测试数据: #2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2 (col3 varchar(2),col4 varchar(5))
insert into #2
select 'aa','1,5,8' union all
select 'bb','2,1,4' union all
select 'cc','3,4,6';with
T (col1,P1,P2) as
(
select col1,charindex(',',','+col2),charindex(',',col2+',')+1 from #1
union all
select a.col1,b.P2,charindex(',',col2+',',b.P2)+1 from #1 a join T b on a.col1=b.col1 where charindex(',',col2+',',b.P2)>0
),
T1 as
(
select a.col1,col2=substring(a.col2+',',b.P1,b.P2-b.P1-1) from #1 a join T b on a.col1=b.col1
)
select * from #2 t where exists (select 1 from T1 where col1='a' and charindex(','+col2+',',','+t.col4+',')>0)
--自己测试第二条col1='b'
--select * from #2 t where exists (select 1 from T1 where col1='b' and charindex(','+col2+',',','+t.col4+',')>0)/*
col3   col4
aa     1,5,8
bb     2,1,4
cc     3,4,6
*/

create table tb1(col1 varchar(10) , col2 varchar(10))
insert into tb1 values('a' , '2,3,5')
insert into tb1 values('b' , '5,3')
create table tb2(col3 varchar(10) , col4 varchar(10))
insert into tb2 values('aa' , '1,5,8')
insert into tb2 values('bb' , '2,1,4')
insert into tb2 values('cc' , '3,4,6')
go--sql server 2000下需要使用临时表
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b select o.* , n.* from
(
  SELECT A.col1, col2 = SUBSTRING(A.[col2], B.id, CHARINDEX(',', A.[col2] + ',', B.id) - B.id) FROM tb1 A, # B WHERE SUBSTRING(',' + A.[col2], B.id, 1) = ','
) m , tb1 o , tb2 n
where charindex(','+m.col2+',',','+n.col4+',') > 0 and m.col1 = o.col1
order by o.col1 , o.col2 , n.col3 , n.col4drop table tb1,tb2,#/*
col1       col2       col3       col4
---------- ---------- ---------- ----------
a          2,3,5      aa         1,5,8
a          2,3,5      bb         2,1,4
a          2,3,5      cc         3,4,6
b          5,3        aa         1,5,8
b          5,3        cc         3,4,6（所影响的行数为 5 行）
*/

我一楼的为单个字符串的查询,八楼为对应表的查询.以下包含sql server 2005的方法,不需要使用临时表.分拆列值原著：邹建
改编：爱新觉罗.毓华  2007-12-16  广东深圳有表tb, 如下:
id          value
----------- -----------
1           aa,bb
2           aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id          value
----------- --------
1           aa
1           bb
2           aaa
2           bbb
2           ccc1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','DROP TABLE #2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
SELECT A.id, B.value
FROM(
    SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)BDROP TABLE tb/*
id          value
----------- ------------------------------
1           aa
1           bb
2           aaa
2           bbb
2           ccc(5 行受影响)
*/

调试易

sql 如何实现这样的功能？

解决方案 »