一个棘手的sql问题....高手请进来...............
0
悬赏园豆：50
[已解决问题]
解决于 2010-08-05 15:45
现在我有A表
字段 id type
123 1;2
223 1;2;3
243 3
244 4
要得到的结果：
B 表
id type groups
123 1 0
123 2 1
223 1 0
223 2 1
223 3 2
243 3 0
244 4 0
..............................................
...................................
数万量级的数据，请问这么实现？
最佳答案
0
字段你再看看对不对，没有细细的查对，嘿嘿
declare
@CurID int,
@GroupCount int,
@SubName nvarchar(50),--截取出来的字段
@SplitName nvarchar(50) --被分号分割的字段
set @GroupCount=0
begin
DECLARE cur CURSOR FOR
SELECT nameid from testname
OPEN cur
FETCH NEXT FROM cur into @CurID
WHILE @@FETCH_STATUS = 0
begin
select @SplitName =type from A where ID=@CurID
if(CHARINDEX(';',@SplitName )>0)
begin
while(CHARINDEX(';',@SplitName )>0)
begin
SELECT @SubName = LTRIM(RTRIM(SUBSTRING(@SplitName ,1,CHARINDEX(';',@SplitName )-1)))
INSERT INTO B(ID,type,groups)values(@CurID,@SubName,@GroupCount)
SET @SplitName = SUBSTRING(@SplitName ,CHARINDEX(';',@SplitName ) + 1,LEN(@SplitName))
SET @GroupCount = @GroupCount + 1
end
INSERT INTO B(ID,type,groups)
values(@CurID,@SplitName,@GroupCount)
end
else
begin
insert into B select id,type,0 from TestName where ID=@CurID
end
FETCH NEXT FROM cur into @CurID
set @GroupCount=0
end
CLOSE cur
DEALLOCATE cur
end
收获园豆：20
其他回答(3)
0
你的groups字段表示的意思是什麽？
给你一个笨方法，前提是你的Type不会太多。我想既然是Type，应该不会太多吧~~
select id,'1' from 你的表 where instr(type,'1') > 0
union
select id,'2' from 你的表 where instr(type,'2') > 0
union
select id,'3' from 你的表 where instr(type,'3') > 0
union
select id,'4' from 你的表 where instr(type,'4') > 0
还有什么不明白的吗？能帮我解决吗
0
提供一个想法,用UNPIVOT实现.
create table Cade
(
ID int,
stype varchar(20)
)
insert into Cade values
(123,'1;2'),
(223,'1;2;3'),
(243,'3'),
(244,'4')
select * from (
select Id,stype,sgroup from (
select ID,LEFT(stype,1) as [0],substring(stype,3,1) as [1],substring(stype,5,1) as [2]
from Cade
)P
UNPIVOT
(
stype
for sgroup in ([0],[1],[2])
) as UNPVT
)A where a.stype<>''
收获园豆：20
虽然对我来说还不这么实用，不过还是谢谢各位的提供帮助。
0
with tmp as (
select 123 as id ,'1;2' as type union all
select 223,'1;2;3' union all
select 243,'3' union all
select 244,'4'
),
cte as (
select
0 as level ,
id,
cast(type+'' as varchar(50)) tmptype,
cast('0' as varchar(50)) as groups
from tmp
union all
select
level+1 as level ,
c.id,
cast(substring(c.tmptype,CHARINDEX(';',c.tmptype)+1,LEN(c.tmptype)) as varchar(50)) as tmptype,
cast(substring(c.tmptype,1,CHARINDEX(';',c.tmptype)-1) as varchar(50)) as groups
from tmp t ,cte c
where t.id=c.id
and CHARINDEX(';',c.tmptype)>0
)
select id,substring(tmptype+';',0,CHARINDEX(';',tmptype+';')) as type,groups
from cte
order by id,level
id type groups
----- -------- ---------
123 1 0
123 2 1
223 1 0
223 2 1
223 3 2
243 3 0
244 4 0
hiahia one shot one kill
收获园豆：10
