求一SQL语句的多种方案

sky6228

现有表A
ID   Name   Class
-------------------
12   a      34||35||36
13   b      34||36
14   c      33||34
15   d      34||35
16   e      34
17   f      34||37

求一SQL语句，希望能够得到下列查询结果：
ID   Name   Class
------------------
12   a      34
12   a      35
12   a      36
13   b      34
13   b      36
14   c      33
14   c      34
15   d      34
15   d      35
16   e      34
17   f      34
18   f      37

如果能够提供多种方案更好

prajna

--建个函数，以下为引用

CREATE FUNCTION f_splitSTR(
@s   varchar(8000),   --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split)-1
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END

fanfan1980

create table T(ID int,  Name char(1),  Class varchar(50))
insert T select 12,   'a',      '34||35||36'
union all select 13,   'b',      '34||36'
union all select 14,   'c' ,     '33||34'
union all select 15,   'd',      '34||35'
union all select 16,   'e',     '34'
union all select 17,   'f',      '34||37'

declare @sql varchar(8000)
set @sql=''
declare @ID int, @Name char(1), @Class varchar(50)
declare T_cursor cursor for
select  ID, Name, Class from T
open T_cursor

fetch next from T_cursor into @ID, @Name, @Class
while @@fetch_status=0
begin
        select @sql=@sql+' select '+rtrim(@ID)+','''+@Name+''','

        select @sql=@sql+replace(@Class, '||', ' union all select '+rtrim(@ID)+','''+@Name+''',')

        select @sql=@sql+' union all '
        fetch next from T_cursor into @ID, @Name, @Class
end
select  @sql=left(@sql, len(@sql)-9)
exec(@sql)
close T_cursor
deallocate T_cursor

sky6228

游标是好，不过不过。。。有不用游标的么

fanfan1980

因為還要保留ID, Name字段, 所以不用遊標可能不行

lilily

建立字符串分割函数,然后用cross apply 进行关联
引用:/*
prajna   
   
--建个函数，以下为引用

CREATE FUNCTION f_splitSTR(
@s   varchar(8000),   --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split)-1
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
*/
select * from A
cross apply f_splitSTR(Class,'||')

kxiaper

create table test_1(id int, name char(10), class varchar(50))
go
insert test_1
select 12,  'a',  '34||35||36'        union all
select 13,  'b',  '34||36'                union all
select 14,  'c' , '33||34'                union all
select 15,  'd',  '34||35'                union all
select 16,  'e',  '34'                        union all
select 17,  'f',  '34||37'
go

create table test_2(id int, name char(10), class varchar(50))
go

select  1
while  @@rowcount > 0       
--当执行begin...end语句块时，执行后影响的行数为0表示已将test_1表的lessons、score1、score2字段清空，循环完毕  
begin  
        insert test_2
        select  id,name,class
        from  test_1  
        where  charindex('|',class)=0 and class <>''

        insert test_2
        select  id,name,left(class,charindex('|',class)-1) as class         -- 取第一个class名称   
        from  test_1  
        where  charindex('|',class)>0

        update test_1  set  class  =  ''       
        where  charindex('|',class)=0

        update test_1  set  
        class  =  substring(class,charindex('|',class)+2,len(class)-charindex('|',class))        -- 取除第一个class外的所有class   
        where  charindex('|',class)>0
end
go

select * from test_2
order by id,name,class

drop table test_1,test_2

/*
id       name              class
12        a                 34
12        a                 35
12        a                 36
13        b                 34
13        b                 36
14        c                 33
14        c                 34
15        d                 34
15        d                 35
16        e                 34
17        f                 34
17        f                 37
*/

flashhw

select * from (select id,name,class=case when len(class)>=2 then substring(class,1,2) end
from t where class is not null
union all
select id,name,class=case when len(class)>=6 then substring(class,5,2) end
from t where class is not null
union all
select id,name,class=case when len(class)>=10 then substring(class,9,2) end
from t where class is not null) tt
where tt.class is not null

kxiaper

--建立测试数据
create table test_1(id int, name char(10), class varchar(50))
go
insert test_1
select 12,  'a',  '34||35||36'        union all
select 13,  'b',  '34||36'                union all
select 14,  'c' , '33||34'                union all
select 15,  'd',  '34||35'                union all
select 16,  'e',  '34'                        union all
select 17,  'f',  '34||37'
go

create table test_2(id int, name char(10), class varchar(50))
go

--执行更新并将结果循环插入临时表test_2
select  1
while  @@rowcount > 0       
--当执行begin...end语句块时，执行后影响的行数为0表示已将test_1表的class字段清空，循环完毕  
begin  
        insert test_2
        select  id,name,class
        from  test_1  
        where  charindex('|',class)=0 and class <>''

        insert test_2
        select  id,name,left(class,charindex('|',class)-1) as class -- 取第一个class名称   
        from  test_1  
        where  charindex('|',class)>0

        update test_1  set  class  =  ''       
        where  charindex('|',class)=0

        update test_1  set  
        class  =  substring(class,charindex('|',class)+2,len(class)-charindex('|',class))-- 取除第一个class外的所有class   
        where  charindex('|',class)>0
end
go
--查看临时表结果
select * from test_2
order by id,name,class

--删除测试环境
drop table test_1,test_2

--临时表结果
/*
id   name      class
12        a                 34
12        a                 35
12        a                 36
13        b                 34
13        b                 36
14        c                 33
14        c                 34
15        d                 34
15        d                 35
16        e                 34
17        f                 34
17        f                 37
*/

wtyrambo

create table #temp
(
ID numeric,
name varchar(10),
class varchar(20)
)

insert into #temp values(12,'a','34||35||36')
insert into #temp values(13,'b','34||36')
insert into #temp values(14,'c','33||34')
insert into #temp values(15,'d','34||35')
insert into #temp values(16,'e','34')
insert into #temp values(17,'f','34||37')

Declare @ID numeric
Declare @name varchar(10)
Declare @class varchar(20)
Declare @position int

create table #results
(
ID numeric,
name varchar(10),
class varchar(20)
)

Declare temp_cursor Cursor for
   select * from #temp

Open temp_cursor
Fetch next From temp_cursor into @ID,@name,@class
while @@fetch_status=0
Begin
  set @position=charindex('||',@class)
  
  if(@position=0)
  insert into #results values(@ID,@name,@class)  

  while(@position>0)
  Begin
    insert into #results values(@ID,@name,substring(@class,1,@position-1))
    set @class=substring(@class,@position+2,len(@class))
    set @position=charindex('||',@class)
   
    if(@position=0)
    Begin
       insert into #results values(@ID,@name,@class)
       Break
    End
  End
Fetch next From temp_cursor into @ID,@name,@class
End

Close temp_cursor
Deallocate temp_cursor

select * from #results

最后结果:
12        a        34
12        a        35
12        a        36
13        b        34
13        b        36
14        c        33
14        c        34
15        d        34
15        d        35
16        e        34
17        f        34
17        f        37