讯享网

一道存储过程面试题 
users1


　　name    company    company_address   url1 
　　Joe     ABC        Work Lane        abc.com;xyz.com 
　　Jill    XYZ        Job Street       abc.com;xyz.com 
写存储过程来创建新表
users2 
　　name company    company_address    url1


　　Joe  ABC     Work Lane          abc.com
    Joe         ABC        Work Lane          xyz.com 
　　Jill        XYZ        Job Street         abc.com 
    Jill        XYZ        Job Street         xyz.com 
===========================================================================================================
drop table users1
go
create table users1 (name varchar(10),company varchar(10),company_address varchar(20),url1 varchar(20))
insert into users1
select ‘Joe’,‘ABC’,‘Work Lane’,‘abc.com;xyz.com’
union all select ‘Jill’,‘XYZ’,‘Job Street’,‘abc.com;xyz.com’


drop proc up_test
go
create proc up_test
as
if exists(select 1 from sysobjects where type=‘U’ and name=‘users2’)
drop table users2
select * into users2 
from (
select name,company,company_address,left(url1,charindex(‘;’,url1)-1) as url1
from users1
union all
select name,company,company_address,right(url1,len(url1)-charindex(‘;’,url1))
from users1)t
order by name,company,company_address
GO
exec up_test


select * from users2
/*
name       company    company_address      url1                 
———- ———- ——————– ——————– 
Jill       XYZ        Job Street           abc.com
Jill       XYZ        Job Street           xyz.com
Joe        ABC        Work Lane            xyz.com
Joe        ABC        Work Lane            abc.com


（所影响的行数为 4 行）
*/


============================================================================================================


–應該寫function,然後根據;拆分紀錄


GO
–FUNCTION
Create    FUNCTION SplitList
(@separator char(1), @List varchar(8000))
RETURNS @ReturnTable TABLE(ListItem varchar(1000) COLLATE Database_Default)
AS 
BEGIN
DECLARE @Index int
DECLARE @NewText varchar(8000) 
IF @List = null
RETURN
SET @Index = CHARINDEX(@separator, @List)
WHILE NOT(@Index = 0)
BEGIN
SET @NewText = RTRIM(LTRIM(LEFT(@List, @Index - 1)))
SET @List = RIGHT(@List, LEN(@List) - @Index)
INSERT INTO @ReturnTable(ListItem) VALUES(@NewText)
SET @Index = CHARINDEX(@separator, @List)
    END
 
INSERT INTO @ReturnTable(ListItem) VALUES(RTRIM(LTRIM(@List)))
         
     RETURN
END


GO
–測試數據
create table users1(name  varchar(10) ,  company  varchar(20),  company_address  varchar(20), url1 varchar(100) )
insert into users1 select    ‘Joe’   ,‘ABC’  ,‘Work Lane’  ,‘abc.com;xyz.com’ 
insert into users1 select   ‘Jill’   ,‘XYZ’  ,‘Job Street’ , ‘abc.com;xyz.com’


create table users2(name  varchar(10) ,  company  varchar(20),  company_address  varchar(20), url1 varchar(100) )
GO
–存儲過程


Create procedure dbo.usp_test
AS


declare @name  varchar(10) ,  @company  varchar(20),  @company_address  varchar(20), @url1 varchar(100)
declare c1 cursor for 
select * from users1 
open c1 
fetch next from c1 into @name,@company,@company_address,@url1
while @@fetch_status=0
begin
  insert into users2
   select @name,@company,@company_address,ListItem from dbo.SplitList(‘;’,@url1) T
  fetch next from c1 into @name,@company,@company_address,@url1
end
close c1
deallocate c1


GO


exec dbo.usp_test
select * from users2
/*
name    company      company_address           url1
Joe ABC  Work Lane            abc.com
Joe ABC  Work Lane            xyz.com
Jill XYZ  Job Street         abc.com
Jill XYZ  Job Street          xyz.com


*/


drop table users1,users2
drop proc usp_test
drop function splitlist