如何提取两个字符串中相同的特定的字段

[linkyou]

表内容如下a，问题是将module字段中uid相同的（这里就是第一第二行），如果以都好格开的字符串相同就提取出来，也就是要得到b表的意思
a
U_ID R_MOdule
----------- --------------------------------------------------
4 8,9,10,11,12,15,16,17,18,19,20,21,22,23,24,25,28
4 1,8,14,18
8 1,8,14,18
b
U_ID R_Module
4 8,18
8 1,8,14,18
请问sql语句如何写,谢谢

[zjcxc]

--处理的示例数据
create table tb(U_ID int,R_MOdule varchar(8000))
insert tb select 4,'aa,8,9,10,11,12,15,16,17,18,19,20,21,22,23,24,25,28'
union all select 4,'1,8,14,18,aa'
union all select 4,'1,8,14,18,17,aa'
union all select 8,'1,8,14,18'
go

--合并数据增加的辅助表
select top 8000 id=identity(int) into 序数表 from syscolumns a,syscolumns b
go

--合并处理函数
create function f_merg(@U_ID int)
returns varchar(8000)
as
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+s
from(
select top 100 percent
s=substring(a.R_MOdule,b.id,charindex(',',a.R_MOdule+',',b.id)-b.id)
from tb a,序数表 b
where a.U_ID=@U_ID and b.id<=len(a.R_MOdule) and substring(','+a.R_MOdule,b.id,1)=','
group by substring(a.R_MOdule,b.id,charindex(',',a.R_MOdule+',',b.id)-b.id)
having count(*)=(select count(*) from tb where U_ID=@U_ID)
order by min(id))a
return(stuff(@s,1,1,''))
end
go

--调用函数进行处理
select U_ID,R_MOdule=dbo.f_merg(U_ID) from tb group by U_ID
go

--删除测试
drop table tb,序数表
drop function f_merg


/*--结果

U_ID R_MOdule
----------- ----------------
4 aa,8,18
8 1,8,14,18

--*/

[zjcxc]

我的函数一样是通用的,只是变量没有带过去而已,并不规定你的序数列中是数字还是字母汉字

[linkyou]

up

[zjcxc]

--处理的示例数据
create table tb(U_ID int,R_MOdule varchar(8000))
insert tb select 4,'8,9,10,11,12,15,16,17,18,19,20,21,22,23,24,25,28'
union all select 4,'1,8,14,18'
union all select 4,'1,8,14,18,17'
union all select 8,'1,8,14,18'
go

--合并数据增加的辅助表
select top 8000 id=identity(int) into 序数表 from syscolumns a,syscolumns b
go

--合并处理函数
create function f_merg(@U_ID int)
returns varchar(8000)
as
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+s
from(
select top 100 percent
s=substring(a.R_MOdule,b.id,charindex(',',a.R_MOdule+',',b.id)-b.id)
from tb a,序数表 b
where a.U_ID=4 and b.id<=len(a.R_MOdule) and substring(','+a.R_MOdule,b.id,1)=','
group by substring(a.R_MOdule,b.id,charindex(',',a.R_MOdule+',',b.id)-b.id)
having count(*)=(select count(*) from tb where U_ID=4)
order by min(id))a
return(stuff(@s,1,1,''))
end
go

--调用函数进行处理
select U_ID,R_MOdule=dbo.f_merg(U_ID) from tb group by U_ID
go

--删除测试
drop table tb,序数表
drop function f_merg


/*--结果

U_ID R_MOdule
----------- ------------------
4 8,18
8 8,18

（所影响的行数为 2 行）
--*/

[xluzhong]

修改老大的一点点小错:)
create function f_merg(@U_ID int)
returns varchar(8000)
as
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+s
from(
select top 100 percent
s=substring(a.R_MOdule,b.id,charindex(',',a.R_MOdule+',',b.id)-b.id)
from tb a,序数表 b
where a.U_ID=@U_ID and b.id<=len(a.R_MOdule) and substring -----将原来---的4改为@U_ID
(','+a.R_MOdule,b.id,1)=','
group by substring(a.R_MOdule,b.id,charindex(',',a.R_MOdule+',',b.id)-b.id)
having count(*)=(select count(*) from tb where U_ID=@U_ID) -----将原-----来的4改为@U_ID
order by min(id))a
return(stuff(@s,1,1,''))
end
go

[linkyou]

非常感谢，不过好像结果和我的不太一样，我u_ID=8那行，R_Module没有变啊，我自己再看看

[zhangzs8896]

老大厉害！
不过，楼主意思似乎是U_ID不重复的原样输出吧。

--调用函数进行处理
select * from(
select U_ID,R_MOdule=dbo.f_merg(U_ID) from tb group by U_ID
having count(R_MOdule)>1
union
select U_ID,R_MOdule=max(R_MOdule) from tb
group by U_ID
having count(R_MOdule)=1
) a

老大的的数据，运行结果：
U_ID R_Module
4 8,18
8 1,8,14,18

[linkyou]

谢谢两位

[xluzhong]

修改老大的一点点小错:)

create function f_merg(@U_ID int)
returns varchar(8000)
as
begin
declare @s nvarchar(4000)
set @s=''
select @s=@s+','+s
from(
select top 100 percent
s=substring(a.R_MOdule,b.id,charindex(',',a.R_MOdule+',',b.id)-b.id)
from tb a,序数表 b
where a.U_ID=@U_ID and b.id<=len(a.R_MOdule) and substring(','+a.R_MOdule,b.id,1)=','-----将原来---的4改为@U_ID
group by substring(a.R_MOdule,b.id,charindex(',',a.R_MOdule+',',b.id)-b.id)
having count(*)=(select count(*) from tb where U_ID=@U_ID)-----将原来---的4改为@U_ID
order by min(id))a
return(stuff(@s,1,1,''))
end
go

[Softlee81307]

create table a(U_ID int, R_MOdule varchar(300))
insert into a
select 4, '8,9,10,11,12,15,16,17,18,19,20,21,22,23,24,25,28' union all
select 4, '1,8,14,18' union all
select 8, '1,8,14,18'
--------------實現語句--------------------

select * from a
-------------建函數處理----------------
Create function bj(@str1 varchar(300),@str2 varchar(300))
returns int
as
begin
declare @s varchar(500),@p int
set @s=''
set @p=0
while charindex(',',@str1)>-1
begin
if charindex(',',@str1)=0
begin
if @str1=@str2
set @p=1
break
end
if substring(@str1,1,charindex(',',@str1)-1)=@str2
begin
set @p=1
break
end
set @str1=substring(@str1,charindex(',',@str1)+1,len(@str1))
end
return(@p)
end
---------------------函數2處理----------------
Create function collatestr(@str1 varchar(300),@str2 varchar(300))
returns varchar(500)
as
begin
declare @s varchar(500),@p varchar(300)
set @s=''
set @p=''
while charindex(',',@str2)>-1
begin
if charindex(',',@str2)=0
begin
if dbo.bj(@str1,@str2)>0
set @p=@p+','+@str2
break
end
if dbo.bj(@str1,substring(@str2,1,charindex(',',@str2)-1))>0
set @p=@p+','+substring(@str2,1,charindex(',',@str2)-1)
set @str2=substring(@str2,charindex(',',@str2)+1,len(@str2))
end
set @p=substring(@p,2,len(@p) )
return(@p)
end
---------------end funtion ------------------
---------------建過程處理---------------------
Create Proc kkk
as
select *,bb=0 into #b from a where u_id in
(select u_id from a group by u_id having count(*)>1)
----------------放臨時表處理-----------
Create table b(u_id int,r_module varchar(300) )
declare @uid int,@i int
set @i=0
update #b set bb=@i,@i=(case when @uid=u_id then @i+1 else 1 end),@uid=u_id
----------結果-------------------------------
insert into b(u_id,r_module)
select k.u_id,dbo.collatestr(k.r_module,g.r_module) from (select * from #b where bb=1) k
inner join (select * from #b where bb=2) g
on k.u_id=g.u_id union all
select u_id,r_module from a where u_id in
(select u_id from a group by u_id having count(*)=1)
--------------------------------------
select * from b order by u_id ----輸出b
drop table b
--------------過程結束-------------------------
exec kkk ---------執行過程
--------輸出結果-------------
4 8,18
8 1,8,14,18

[yyyjff]

好像不对哦

[linkyou]

0,80,20,0,0,0,0
贴子回复次数大于跟给分次数
为什么结不了贴，看来要改天给分了

[zhangzs8896]

因为你结帖子时正好也有人给你回答帖子，呵呵

[zjcxc]

我的函数写错了一点嘛,按 xluzhong(打麻将一缺三，咋办？) 改的改一下函数就行了

[linkyou]

还有一问如果逗号之间不是自然数，是无规则字符串呢，当然字符串也可以弄张表把它所有的情况存起来

[linkyou]

还是看不太懂
select top 100 percent
s=substring(a.R_MOdule,b.id,charindex(',',a.R_MOdule+',',b.id)-b.id)
from tb a,序数表 b
where a.U_ID=4 and b.id<=len(a.R_MOdule) and substring(','+a.R_MOdule,b.id,1)=','
能解释一下吗，特别是b.Id怎么可以作为substring的startpos呢

[linkyou]

同时感谢Softlee81307(孔腎)
你的方法更加具备普遍意义，这里高手真多

[heguosheng]

收藏