print '================================================================================
初始化資料庫：
================================================================================'
USE master
GO
IF EXISTS(SELECT * FROM sysdatabases WHERE name='aDB')
DROP DATABASE aDB
GO
EXEC XP_cmdshell 'mkdir D:\project',no_output
CREATE DATABASE aDB
ON
(
NAME='aDB_data',
FILENAME='D:\project\aDB_data.mdf',
SIZE =10 MB,
FILEGROWTH=20%
)
LOG ON
(
NAME='aDB_log',
FILENAME='d:\project\aDB_log.ldf',
SIZE =20 MB,
FILEGROWTH=10%
)
GO
print '================================================================================
開始：
================================================================================'
USE aDB
GO
SET NOCOUNT ON
IF EXISTS(SELECT * FROM sysobjects WHERE name='Books')
DROP table Books
GO
create table Books
(
ID int, --書籍編號
Author int, --作者編號
Title varchar(100) --書名
)
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='Authors')
DROP table Authors
GO
Create table Authors
(
ID int, --作者編號
Name varchar(20) --姓名
)
declare @n int,@m int
select @n=1,@m=1
while (@n<=5)
begin
insert into Authors values(@n,substring(replace(newid(),'-',''),1,5))
set @n=@n+1
end
while (@m<=10)
begin
insert into Books values(@m,cast(rand()*5 as int)+1,substring(replace(newid(),'-',''),1,10)) --一個作者可以有多本書
set @m=@m+1
end
if exists (select * from sysobjects where name = 'newtable')
drop table newtable
GO
create table newtable
(
ID int, --作者編號
Name varchar(20), --姓名
Title varchar(1000) --擁有的書名
)
declare @name varchar(20)
declare @id int
DECLARE author_Cursor CURSOR FOR
SELECT ID, Name
FROM Authors
OPEN author_Cursor
FETCH NEXT FROM author_Cursor into @id,@name
WHILE @@FETCH_STATUS = 0
BEGIN
declare @bid int
declare @title varchar(50)
declare @str varchar(1000)
set @str=''
DECLARE book_Cursor CURSOR FOR
SELECT ID, Title
FROM Books
WHERE Author = @id
OPEN book_Cursor
FETCH NEXT FROM book_Cursor into @bid,@title
WHILE @@FETCH_STATUS = 0
BEGIN
set @str = @str + ' 《' + @title+ '》'
FETCH NEXT FROM book_Cursor into @bid,@title
END
CLOSE book_Cursor
DEALLOCATE book_Cursor
insert into newtable values (@id,@name,@str)
FETCH NEXT FROM author_Cursor into @id,@name
END
CLOSE author_Cursor
DEALLOCATE author_Cursor
print '================================================================================
全部作者資訊：
================================================================================'
select 作者編號=ID,作者姓名=Name,寫過的書籍=Title from newtable
GO
print '================================================================================
寫過2本書以上的作者資訊：
================================================================================'
select 作者編號=ID,作者姓名=Name,寫過的書籍=Title from newtable N
where
(select count(*) from Books B where B.Author=N.ID)>=2
print '================================================================================
作者表：
================================================================================'
select * from Authors
print '================================================================================
書表：
================================================================================'
select * from Books