sql server2008 数据处理问题 高手进
我的目的是 把 第一个表中的选择题选项 通过‘，’ 打散 通过第一个表的ID 和第二个表的OptionId做关联 ，把选项循环插入到第二个表中 Option中 希望用游标 能帮我实现楼
提示：选项的长度都是固定的 六个选择题； sql?server，循环游标
[解决办法]
更新还要帮你写不？
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-06 16:38:11
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[tname] varchar(7),[toption] varchar(11))
insert [huang]
select 1,'选择题1','A,B,C,B,C,D' union all
select 2,'选择题2','A,B,C,B,C,D'
--------------开始查询--------------------------
select
id,
a.[tname],
SUBSTRING([toption],number,CHARINDEX(',',[toption]+',',number)-number) as [toption]
from
[huang] a,master..spt_values
where
number >=1 and number<=len([toption])
and type='p'
and substring(','+[toption],number,1)=','
----------------结果----------------------------
/*
id tname toption
----------- ------- -----------
1 选择题1 A
1 选择题1 B
1 选择题1 C
1 选择题1 B
1 选择题1 C
1 选择题1 D
2 选择题2 A
2 选择题2 B
2 选择题2 C
2 选择题2 B
2 选择题2 C
2 选择题2 D
*/
create table tab1
(id int,TName varchar(20),TOption varchar(20))
create table tab2
(Id int,TOptionId int,Options varchar(20))
insert into tab1
select 1,'选择题1','A,B,C,B,C,D' union all
select 2,'选择题2','A,B,C,B,C,D'
insert into tab2
select 1,1,null union all
select 2,1,null union all
select 3,1,null union all
select 4,1,null union all
select 5,1,null union all
select 6,1,null union all
select 7,2,null union all
select 8,2,null union all
select 9,2,null union all
select 10,2,null union all
select 11,2,null union all
select 12,2,null
-- 更新
update a
set a.Options=b.TOptions
from
(select TOptionId,Options,
row_number() over(partition by TOptionId order by Id) 'rn'
from tab2) a
inner join
(select a.id,
substring(a.TOption,b.number,charindex(',',a.TOption+',',b.number)-b.number) 'TOptions',
row_number() over(partition by a.id order by getdate()) 'rn'
from tab1 a
inner join master.dbo.spt_values b
on b.type='P' and b.number between 1 and len(a.TOption)
and substring(','+a.TOption,b.number,1)=',') b on a.TOptionId=b.id and a.rn=b.rn
-- 查询
select * from tab2
/*
Id TOptionId Options
----------- ----------- --------------------
1 1 A
2 1 B
3 1 C
4 1 B
5 1 C
6 1 D
7 2 A
8 2 B
9 2 C
10 2 B
11 2 C
12 2 D
(12 row(s) affected)
*/
--drop table tab1
--drop table tab2
create table tab1
(id int,TName varchar(20),TOption varchar(20))
create table tab2
(Id int,TOptionId int,Options varchar(20))
insert into tab1
select 1,'选择题1','A,B,C,B,C,D' union all
select 2,'选择题2','A,B,C,B,C,D'
insert into tab2
select 1,1,null union all
select 2,1,null union all
select 3,1,null union all
select 4,1,null union all
select 5,1,null union all
select 6,1,null union all
select 7,2,null union all
select 8,2,null union all
select 9,2,null union all
select 10,2,null union all
select 11,2,null union all
select 12,2,null
go
declare @id varchar(10)
declare @TOption varchar(20)
--定义table1的游标
declare cur_table1 cursor
for select id,TOption from tab1 --用于查询的游标
declare @TOptionId int
declare @Options varchar(20)
--定义table2的游标
declare cur_table2 cursor
for select TOptionId,Options from tab2 for update --可以游标更新的
open cur_table1; --打开游标
--从游标中取数，放到变量中
fetch next from cur_table1 into @id,@TOption
while @@FETCH_STATUS = 0 --外层游标cur_table1的遍历
begin
open cur_table2;
fetch next from cur_table2 into @TOptionId,@Options
while @@FETCH_STATUS = 0 --内存游标cur_table2的遍历
begin
if (@id= @TOptionId)
begin
update tab2
set Options = left(@TOption,charindex(',',@TOption+',')-1)
where current of cur_table2;
set @TOption = stuff(@TOption,1,charindex(',',@TOption+','),'')
end
fetch next from cur_table2 into @TOptionId,@Options
end
close cur_table2; --关闭内层游标
fetch next from cur_table1 into @id,@TOption
end
close cur_table1; --关闭游标
deallocate cur_table2; --释放游标cur_table2的资源
deallocate cur_table1; --释放游标cur_table1的资源
--查询更新后的结果
select *
from tab2
/*
IdTOptionIdOptions
11A
21B
31C
41B
51C
61D
72A
82B
92C
102B
112C
122D
*/
create table tab1
(id int,TName varchar(20),TOption varchar(20))
create table tab2
(Id int,TOptionId int,Options varchar(20))
insert into tab1
select 1,'选择题1','A,B,C,B,C,D' union all
select 2,'选择题2','A,B,C,B,C,D'
insert into tab2
select 1,1,null union all
select 2,1,null union all
select 3,1,null union all
select 4,1,null union all
select 5,1,null union all
select 6,1,null union all
select 7,2,null union all
select 8,2,null union all
select 9,2,null union all
select 10,2,null union all
select 11,2,null union all
select 12,2,null
-- 用游标更新
set nocount on
declare @id int,@TName varchar(20),@TOption varchar(20)
declare ap scroll cursor for
select id,TName,TOption from tab1
open ap
fetch first from ap into @id,@TName,@TOption
while(@@fetch_status<>-1)
begin
update a
set a.Options=b.TOptions
from
(select TOptionId,Options,
row_number() over(partition by TOptionId order by Id) 'rn'
from tab2) a
inner join
(select a.id,
substring(a.TOption,b.number,charindex(',',a.TOption+',',b.number)-b.number) 'TOptions',
row_number() over(partition by a.id order by getdate()) 'rn'
from (select @id 'id',@TName 'TName',@TOption 'TOption') a
inner join master.dbo.spt_values b
on b.type='P' and b.number between 1 and len(a.TOption)
and substring(','+a.TOption,b.number,1)=',') b on a.TOptionId=b.id and a.rn=b.rn
fetch next from ap into @id,@TName,@TOption
end
close ap
deallocate ap
set nocount off
-- 结果
select * from tab2
/*
Id TOptionId Options
----------- ----------- --------------------
1 1 A
2 1 B
3 1 C
4 1 B
5 1 C
6 1 D
7 2 A
8 2 B
9 2 C
10 2 B
11 2 C
12 2 D
(12 row(s) affected)
*/
