求一SQL語句 在線等

Question

--sql server 2000 用临时表+函数完成
create table tb (o_no varchar(50),idno varchar(12)) 
insert into tb values('09-171,09-172,09-171', 'A2008030050')
insert into tb values('8008,8009,8009', 'A2008030051')
insert into tb values('8010,8010', 'A2008030052')
insert into tb values('1485,1857', 'A2008030053')
goSELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO tmp FROM syscolumns a, syscolumns b go--创建一个合并的函数
create function f_hb(@idno varchar(12))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(o_no as varchar) from 
  (
    SELECT distinct A.idno, o_no = SUBSTRING(A.[o_no], B.id, CHARINDEX(',', A.[o_no] + ',', B.id) - B.id)
    FROM tb A, tmp B
    WHERE SUBSTRING(',' + A.[o_no], B.id, 1) = ','
  ) t
  where idno = @idno
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct idno ,dbo.f_hb(idno) as o_no from 
(
  SELECT distinct A.idno, o_no = SUBSTRING(A.[o_no], B.id, CHARINDEX(',', A.[o_no] + ',', B.id) - B.id)
  FROM tb A, tmp B
  WHERE SUBSTRING(',' + A.[o_no], B.id, 1) = ','
) tdrop table tb , tmp
drop function dbo.f_hb/*
idno         o_no          
------------ --------------
A2008030050  09-172,09-171
A2008030051  8008,8009
A2008030052  8010
A2008030053  1485,1857（所影响的行数为 4 行）
*/

Answer 1

分解 按照条件 合并 o_no

Answer 2

--sql server 2005 用OUTER APPLY完成
create table tb (o_no varchar(50),idno varchar(12)) 
insert into tb values('09-171,09-172,09-171', 'A2008030050')
insert into tb values('8008,8009,8009', 'A2008030051')
insert into tb values('8010,8010', 'A2008030052')
insert into tb values('1485,1857', 'A2008030053')
goSELECT * FROM(SELECT DISTINCT idno FROM 
(
  SELECT distinct A.idno, B.o_no FROM(SELECT idno, [o_no] = CONVERT(xml,'<root><v>' + REPLACE([o_no], ',', '</v><v>') + '</v></root>') FROM tb)A
  OUTER APPLY(SELECT o_no = N.v.value('.', 'varchar(100)') FROM A.[o_no].nodes('/root/v') N(v))B
) T
)P OUTER APPLY(SELECT [o_no]= STUFF(REPLACE(REPLACE((SELECT o_no FROM 
(
  SELECT distinct A.idno, B.o_no FROM(SELECT idno, [o_no] = CONVERT(xml,'<root><v>' + REPLACE([o_no], ',', '</v><v>') + '</v></root>') FROM tb)A
  OUTER APPLY(SELECT o_no = N.v.value('.', 'varchar(100)') FROM A.[o_no].nodes('/root/v') N(v))B
) N
WHERE idno = P.idno FOR XML AUTO), '<N o_no="', ','), '"/>', ''), 1, 1, '')
)Qdrop table tb/*
idno         o_no
------------ -------------
A2008030050  09-171,09-172
A2008030051  8008,8009
A2008030052  8010
A2008030053  1485,1857(4 行受影响)
*/

Answer 3

create table ta(o_no varchar(50),idno varchar(12)) 
insert ta select '09-171,09-172,09-171', 'A2008030050'
union all  select '8008,8009,8009', 'A2008030051'
union all  select '8010,8010', 'A2008030052'
union all  select '1485,1857', 'A2008030053'
go
create function f_re(@no varchar(50))
returns varchar(50)
as
begin
   declare @s nvarchar(4000)
   declare @t table(col varchar(100))
   set @no = @no + ','
   while(charindex(',',@no) > 0)
   begin 
        insert @t select left(@no,charindex(',',@no+',') -1)
        set @no = substring(@no,charindex(',',@no+',') +1,100)
   end
   set @s = ''
   select @s = @s + col +',' from (select distinct col from @t ) a
   set @s = left(@s,len(@s) - 1)
   return @s
   
end
go
select dbo.f_re(o_no) as o_no,idno from ta
drop table ta 
drop function f_re
/*o_no                                               idno         
-------------------------------------------------- ------------ 
09-171,09-172                                      A2008030050
8008,8009                                          A2008030051
8010                                               A2008030052
1485,1857                                          A2008030053（所影响的行数为 4 行）
*/

Answer 4

Create function F_string(@v varchar(50))
returns varchar(50)
as
begin
declare @t table(name varchar(20))
while charindex(',',@v)>0
begin
insert into @t select substring(@v,1,charindex(',',@v)-1)
set @v=substring(@v,charindex(',',@v)+1,len(@v))
endinsert into @t select @v
declare @s varchar(50)
set @s=''
select @s=@s+name+',' from (select distinct name from @t) aif charindex(',',@s)>0
set @s=left(@s,len(@s)-1)
return @send
declare @t table(o_no varchar(50),idno varchar(12)) 
insert @t select '09-171,09-172,09-171', 'A2008030050'
union all  select '8008,8009,8009', 'A2008030051'
union all  select '8010,8010', 'A2008030052'
union all  select '1485,1857', 'A2008030053'
select dbo.F_string(o_no) as n_no,idno from @t n_no                                               idno
-------------------------------------------------- ------------
09-171,09-172                                      A2008030050
8008,8009                                          A2008030051
8010                                               A2008030052
1485,1857                                          A2008030053(4 行受影响)