SQL 逗号分隔符查询

BackerCao 2012-03-23 09:39:39
表1
ID Name
1 a
2 b
3 c


表2
No Value
n1 1,2
n2 2,3
n3 1,2,3
n4 1,3

查询结果:
No Result
n1 a,b
n2 b,c
n3 a,b,c
n4 a,c
...全文
685 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
xsandjava 2013-07-16
  • 打赏
  • 举报
 回复
太强大了
LYFFY 2012-12-25
  • 打赏
  • 举报
 回复
引用 5 楼 BackerCao 的回复:
搞定,结贴!!! SELECT No , STUFF((SELECT ','+ObjectName FROM 表1 WHERE CHARINDEX(','+LTRIM(表1.No)+',',','+表2.Value+',')>0 FOR XML PATH('')),1,1,'') AS Result FROM 表2
为什么我的总是少查一个，是一个id就查不出来呢？
BackerCao 2012-03-23
  • 打赏
  • 举报
 回复
搞定,结贴!!!

SELECT No ,
STUFF((SELECT ','+ObjectName FROM 表1
WHERE CHARINDEX(','+LTRIM(表1.No)+',',','+表2.Value+',')>0
FOR XML PATH('')),1,1,'') AS Result
FROM 表2
99归一 2012-03-23
  • 打赏
  • 举报
 回复 


/*

标题：分解字符串并查询相关数据

作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 

时间：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 + ',') > 0





drop 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 行）

*/
苦逼的程序员 2012-03-23
  • 打赏
  • 举报
 回复
太牛气了。。。学习
dawugui 2012-03-23
  • 打赏
  • 举报
 回复 
分解字符串包含的信息值后然后合并到另外一表的信息

(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-23  广东深圳)



/*问题描述

tba

ID  classid   name

1     1,2,3   西服 

2     2,3     中山装

3     1,3     名裤

tbb 

id   classname

1     衣服

2     上衣

3     裤子



我得的结果是

id   classname            name

1     衣服,上衣,裤子      西服 

2     上衣，裤子          中山装

3     衣服，裤子          名裤

*/



-----------------------------------------------------

--sql server 2000中的写法

create table tba(ID int,classid varchar(20),name varchar(10))

insert into tba values(1,'1,2,3','西服')

insert into tba values(2,'2,3'  ,'中山装')

insert into tba values(3,'1,3'  ,'名裤')

create table tbb(ID varchar(10), classname varchar(10))

insert into tbb values('1','衣服')

insert into tbb values('2','上衣')

insert into tbb values('3','裤子')

go



--第1种方法，创建函数来显示

create function f_hb(@id varchar(10))

returns varchar(1000)

as

begin

  declare @str varchar(1000)

  set @str=''

  select @str=@str+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',','+@id+',')>0

  return stuff(@str,1,1,'')

end

go 

select id,classid=dbo.f_hb(classid),name from tba

drop function f_hb

/*

id          classid       name       

----------- ------------- ---------- 

1           衣服,上衣,裤子 西服

2           上衣,裤子      中山装

3           衣服,裤子      名裤

（所影响的行数为 3 行）

*/



--第2种方法.update

while(exists (select * from tba,tbb where charindex(tbb.id,tba.classid) >0))

update tba

set classid= replace(classid,tbb.id,tbb.classname)

from tbb

where charindex(tbb.id,tba.classid)>0

select * from tba

/*

ID          classid              name       

----------- -------------------- ---------- 

1           衣服,上衣,裤子       西服

2           上衣,裤子            中山装

3           衣服,裤子            名裤

（所影响的行数为 3 行）

*/

drop table tba,tbb



------------------------------------------------------------------------

--sql server 2005中先分解tba中的classid,然后再合并classname

create table tba(ID int,classid varchar(20),name varchar(10))

insert into tba values(1,'1,2,3','西服')

insert into tba values(2,'2,3'  ,'中山装')

insert into tba values(3,'1,3'  ,'名裤')

create table tbb(ID varchar(10), classname varchar(10))

insert into tbb values('1','衣服')

insert into tbb values('2','上衣')

insert into tbb values('3','裤子')

go



SELECT id , classname , name FROM

(

  SELECT DISTINCT id , name FROM (select tbc.id , tbc.name , tbb.classname from 

  (

    SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A

    OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B

  ) tbc , tbb where tbc.classid = tbb.id

  ) T

)A 

OUTER APPLY

(

  SELECT [classname]= STUFF(REPLACE(REPLACE((

    SELECT classname FROM (select tbc.id , tbc.name , tbb.classname from 

    (

      SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A

      OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B

    ) tbc , tbb where tbc.classid = tbb.id

  ) N

  WHERE id = A.id and name = A.name

  FOR XML AUTO), '<N classname="', ','), '"/>', ''), 1, 1, '')

)N

order by id



drop table tba,tbb



/*

id          classname      name

----------- -------------- ----------

1           衣服,上衣,裤子 西服

2           上衣,裤子      中山装

3           衣服,裤子      名裤

(3 行受影响)

*/
阿呆哥 2012-03-23
  • 打赏
  • 举报
 回复
这表设计的太牛啦，试试replace
SQL 将以逗号分隔符的字符串转换为 table 列的函数
将带分隔符的字符串 例如 '1,2,3,4,5,6,7,8,9,10' 转换成table 的列的函数
sqlserver 实现 行转列 split 分割的函数
sqlserver 实现 行转列 split 分割的函数，具体使用方法写有在文件里。
字符串分割的字符串数量 SQL
用 逗号等分隔符 分开的字符串数量 比如 aaa,bbb,ccc 输出3
sql带分隔符的截取字符串示例
主要介绍了sql带分隔符的截取字符串示例,需要的朋友可以参考下
sqlserver中去除字符串中连续的分隔符的sql语句
sqlserver中去除字符串中连续的分隔符的sql语句，需要的朋友可以参考下
MS-SQL Server

34,576

社区成员

254,587

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章