拆分字符串create table tb (ComputerID int, MatColor varchar(20))
insert into tb values(1,'02,03')
insert into tb values(2,'02,06,07')
insert into tb values(3,'01,09')
go
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
SELECT
A.ComputerID,
MatColor = SUBSTRING(A.MatColor, B.ID, CHARINDEX(',', A.MatColor + ',', B.ID) - B.ID)
FROM tb A, # B
WHERE SUBSTRING(',' + a.MatColor, B.id, 1) = ','
ORDER BY 1,2
GOdrop table tb,#/*
ComputerID MatColor
----------- --------------------
1 02
1 03
2 02
2 06
2 07
3 01
3 09*/
insert into tb values(1,'02,03')
insert into tb values(2,'02,06,07')
insert into tb values(3,'01,09')
go
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
SELECT
A.ComputerID,
MatColor = SUBSTRING(A.MatColor, B.ID, CHARINDEX(',', A.MatColor + ',', B.ID) - B.ID)
FROM tb A, # B
WHERE SUBSTRING(',' + a.MatColor, B.id, 1) = ','
ORDER BY 1,2
GOdrop table tb,#/*
ComputerID MatColor
----------- --------------------
1 02
1 03
2 02
2 06
2 07
3 01
3 09*/
不能这么忽悠人啊,你看了我问什么了吗?????
@OptionStr nvarchar(2000)ASdeclare QuestionID intset nocount onbegin transactioninsert into Questions(QuestionName) values (@QuestionNamer)set @QuestionID = @@identityif @@error <> 0begin
rollback raiserror ('Error Insert into Questions!',16,1) return
end
declare @s varchar(8000)//@Optionstr = '苹果 |香蕉 |葡萄 |荔枝' set @s = 'select '+ ltirm(@QuestionID) + ','''+replace(@Optionstr,'|',' '' union all select '''+ ltirm(@QuestionID))+''''set @s = 'select '+ ltrim(@QuestionID) + ','''+replace(@Optionstr,'|',' '' union all select '+ ltrim(@QuestionID)+',''')+''''exec( 'insert into options(questionid,optionname) '+@s)go
create table Options (OptionID int identity(1,1), QuestionID int,OptionName nvarchar(100))
create table Questions(QuestionID int identity(1,1),QuestionName nvarchar(100))
goCREATE PROCEDURE QuestionAndOptionData_Insert
@QuestionName nvarchar(100),
@OptionStr nvarchar(2000)ASdeclare @QuestionID intset nocount on
insert into Questions(QuestionName) values (@QuestionName)set @QuestionID = @@identityif @@error <> 0begin
rollback raiserror ('Error Insert into Questions!',16,1) return
end
declare @s varchar(8000)--@Optionstr = '苹果 |香蕉 |葡萄 |荔枝' set @s = 'select '+ ltrim(@QuestionID) + ','''+replace(@Optionstr,'|',' '' union all select '+ ltrim(@QuestionID)+',''')+''''exec( 'insert into options(questionid,optionname) '+@s)go
exec QuestionAndOptionData_Insert @QuestionName = '你喜欢吃什么?',@Optionstr = '苹果 ¦香蕉 ¦葡萄 ¦荔枝' select * from Options
select * from Questionsdrop table Options,Questions
drop proc QuestionAndOptionData_Insert/*OptionID QuestionID OptionName
----------- ----------- ----------------------------------------------------------------------------------------------------
1 1 苹果
2 1 香蕉
3 1 葡萄
4 1 荔枝(所影响的行数为 4 行)QuestionID QuestionName
----------- ----------------------------------------------------------------------------------------------------
1 你喜欢吃什么?(所影响的行数为 1 行)
*/
drop table Questions
go
if object_id('Options') is not null
drop table Options
go
create table Questions(QuestionID int identity(1,1),QuestionName nvarchar(100))
create table Options(OptionID int identity(1,1), QuestionID int,OptionName nvarchar(100))
--键过程
if object_id('proc_name') is not null
drop proc proc_name
go
create proc proc_name
@QuestionName varchar(100),
@Optionstr varchar(100)
as
begin
declare @QuestionID int
insert into Questions(QuestionName) select @QuestionName
select @QuestionID=@@identity
while charindex('¦',@Optionstr)>0
begin
insert into Options(QuestionID,OptionName) select @QuestionID, left(@Optionstr,charindex('¦',@Optionstr)-1)
set @Optionstr=stuff(@Optionstr,1,charindex('¦',@Optionstr),'')
end
insert into Options(QuestionID,OptionName) select @QuestionID, @Optionstr
end
go
--执行过程
exec proc_name'你喜欢吃什么?','苹果¦香蕉 ¦葡萄 ¦荔枝' select * from Questions
select * from OptionsQuestionID QuestionName
1 你喜欢吃什么?
OptionID QuestionID OptionName
1 1 苹果
2 1 香蕉
3 1 葡萄
4 1 荔枝