首页 新闻 搜索 专区 学院

一个棘手的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

..............................................

...................................

数万量级的数据,请问这么实现?

问题补充: groups是表示分的组,这个以后对我是有用的。就按那样的分。
VIC豆丁的主页 VIC豆丁 | 初学一级 | 园豆:120
提问于:2010-07-21 16:18
< >
分享
最佳答案
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
xihongshibeibei | 菜鸟二级 |园豆:386 | 2010-07-22 13:23
其他回答(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

智会超 | 园豆:1 (初学一级) | 2010-07-21 16:52
还有什么不明白的吗?能帮我解决吗
支持(0) 反对(0) VIC豆丁 | 园豆:120 (初学一级) | 2010-07-21 17:24
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
changbluesky | 园豆:854 (小虾三级) | 2010-07-21 23:11
虽然对我来说还不这么实用,不过还是谢谢各位的提供帮助。
支持(0) 反对(0) VIC豆丁 | 园豆:120 (初学一级) | 2010-07-22 08:58
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
killkill | 园豆:992 (小虾三级) | 2010-07-29 10:11
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册