求一SQL语句的多种方案,一经解决立刻给分！
现有表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
如果能够提供多种方案更好，解决后立刻给分！
[解决办法]
--建个函数，以下为引用
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
[解决办法]
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
[解决办法]
SQL2005方法:
建立字符串分割函数,然后用cross apply 进行关联
引用:/*
jacobsan(梅) ( ) 信誉：100 Blog 2007-1-2 15:40:14 得分: 0
--建个函数，以下为引用
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, '|| ')
[解决办法]
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
12a 34
12a 35
12a 36
13b 34
13b 36
14c 33
14c 34
15d 34
15d 35
16e 34
17f 34
17f 37
*/
[解决办法]
--建立测试数据
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
12a 34
12a 35
12a 36
13b 34
13b 36
14c 33
14c 34
15d 34
15d 35
16e 34
17f 34
17f 37
*/
[解决办法]
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
最后结果:
12a34
12a35
12a36
13b34
13b36
14c33
14c34
15d34
15d35
16e34
17f34
17f37
