立天下

游标的使用(嵌套的游标)

Posted on 2009-08-04 by libra

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 ‘=============================================
书表：
=============================================’

Posted in SQL, 学习 | Tagged 游标 | Leave a comment

随机数与分页

Posted on 2009-08-04 by libra

print ‘=============================================
初始化数据库：
=============================================’

)
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=’A')
DROP table A
GO
create table A
(
ID int identity(1,1) not null,
LastUpdateDate datetime not null
)
declare @n int
set @n=1
while (@n<=50)
begin
insert into A values(dateadd(dd,datediff(dd,’2000-01-01′,’2009-08-3′)*RAND(),’2000-01-01′))
set @n=@n+1
end
print ‘=============================================’
GO

declare @id int,@date datetime
select TOP 1 @id=ID,@date=LastUpdateDate from A order by LastUpdateDate desc
print ‘最近的交易号：’+convert(varchar(5),@id)+’ 日期为’+convert(varchar(10),@date,111)
GO

print ‘=============================================’
print ‘取出表A中第31—40记录：’
print ‘=============================================’
select top 10 * from (select top 40 * from A order by ID ) as a order by ID desc
print ‘=============================================’
print ‘表A的数据如下：’
print ‘=============================================’
select * from A

print ‘=============================================’
print ‘下面是分页的实现：’
print ‘=============================================’

USE aDB
GO
SET NOCOUNT ON

if exists (select * from sysobjects where name = ‘proc_page’)
drop procedure proc_page
GO
create procedure proc_page
@maxPage int=5,           –最多显示页数，默认5页
@rowsNum int=10,         –每页行数，默认10行
@rowsSum int=50         –总行数，默认为50行，随机生成
as
if (not @rowsNum between 1 and @rowsSum) or (@maxPage<= 0 ) or(@rowsSum<= 0 )
begin
raiserror (‘错误！请正确指定各项参数！’,16,1)
return
end
IF EXISTS(SELECT * FROM sysobjects WHERE name=’A')
DROP table A
create table A
(
ID int identity(1,1) not null,
Name varchar(10) not null
)
declare @n int
set @n=1
while (@n<=@rowsSum)
begin
insert into A values(substring(replace(newid(),’-',”),1,5))
set @n=@n+1
end

declare @pages int,@pageNo int,@lastRows int –定义变量总页数，页码，最后一页行数
–select @rowsSum=count(*) from A
select @pages=ceiling(@rowsSum*1.0/@rowsNum)
select @lastRows=@rowsSum%@rowsNum
–select pages=30%4

if (@maxPage<@pages)
begin
select @rowsSum=@rowsNum*@maxPage
set @pageNo = 1
print ‘分页显示:一共’+convert(varchar(10),@rowsSum)+’行，每页行数’+convert(varchar(10),@rowsNum)+’，总页数’+convert(varchar(10),@maxPage)
while (@pageNo<=@maxPage)
begin
print ‘——————-第’+convert(varchar(10),@pageNo)+’页——————’
    if exists (select * from sysobjects where name=’newA’)
    drop table newA

select top (@rowsNum) a.* from (select top (@rowsSum) * from A order by ID) a
where (ID not in (select top (@rowsNum*(@maxPage-@pageNo)) a.ID from a order by a.ID desc))
and
(ID not in (select top (@rowsNum*(@pageNo-1)) a.ID from a order by a.ID))
order by a.ID

set @pageNo=@pageNo+1
end
end

else
begin
set @pageNo = 1
print ‘分页显示:一共’+convert(varchar(10),@rowsSum)+’行，每页行数’+convert(varchar(10),@rowsNum)+’，总页数’+convert(varchar(10),@pages)
if (@lastRows=0)
  begin
   while (@pageNo<=@pages)
   begin
   print ‘——————-第’+convert(varchar(10),@pageNo)+’页——————’

   select top (@rowsNum) * from A
   where (ID not in (select top (@rowsNum*(@pages-@pageNo)) ID from A order by ID desc))
   and
   (ID not in (select top (@rowsNum*(@pageNo-1)) ID from A order by ID))
   order by ID
   set @pageNo=@pageNo+1
   end
  end
else
        begin
            while (@pageNo<=@pages)
   begin
   print ‘——————-第’+convert(varchar(10),@pageNo)+’页——————’
    if(@pageNo<>@pages)
    begin
    select top (@rowsNum) * from A
    where (ID not in (select top (@rowsNum*(@pages-@pageNo-1)+@lastRows) ID from A order by ID desc))
    and
    (ID not in (select top (@rowsNum*(@pageNo-1)) ID from A order by ID))
    order by ID
    end
                else
                select top (@lastRows) * from A
                where (ID not in (select top (@rowsNum*(@pageNo-1)) ID from A order by ID))
                order by ID
   set @pageNo=@pageNo+1
   end
        end
end
GO

EXEC proc_page
EXEC proc_page 20,4
EXEC proc_page 3,5
EXEC proc_page @rowsNum=3
EXEC proc_page 10,4,30
EXEC proc_page 50
EXEC proc_page @rowsNum=100 –产生错误

Posted in SQL, 学习 | Tagged SQL | Leave a comment

交叉表

Posted on 2009-08-04 by libra

************************************************

自己研究的3种静态实现交叉表方法，动态仍在学习。。。
************************************************

************************************************

方案一
************************************************

USE stuDBTest
GO
SET NOCOUNT ON

select stuNo=jHS.stuNo,java成绩=jHS.java,HTML成绩=jHS.HTML,SQL成绩=jHS.SQL,C#成绩=C#.Score
from
(select stuNo=jH.stuNo,java=jH.java,HTML=jH.HTML,SQL=SQL.Score
from
(select stuNo=java.stuNo,java=java.Score,HTML=HTML.Score
from
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName=’Java’)) java,
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName=’HTML’)) HTML
where java.stuNo=HTML.stuNo) jH,
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName=’SQL’)) SQL
where jH.stuNo=SQL.stuNo) jHS,
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName=’C#’)) C#
where jHS.stuNo=C#.stuNo

************************************************

方案二
************************************************

select stuNo=jHSC.stuNo,
java成绩=isnull(jHSC.java,0),
HTML成绩=isnull(jHSC.HTML,0),
SQL成绩=isnull(jHSC.SQL,0),
C#成绩=isnull(jHSC.C#,0),
SQLAdvance成绩=isnull(SQLAdvance.score,0)
from
(select stuNo=jHS.stuNo,java=jHS.java,HTML=jHS.HTML,SQL=jHS.SQL,C#=C#.score
from
(select stuNo=jH.stuNo,java=jH.java,HTML=jH.HTML,SQL=SQL.score
from
(select stuNo=java.stuNo,java=java.Score,HTML=HTML.Score
from
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName=’Java’)) java –看做表 JAVA

left join
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName=’HTML’)) HTML –看做表 HTML
on java.stuNo=HTML.stuNo) jH –连接JAVA和HTML后看做表jH

left join
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName=’SQL’)) SQL    –看做表 SQL
                                                                        on jH.stuNo=SQL.stuNo)            jHS —-连接SQL和jH后看做表jHS
left join
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName=’C#’)) C#      –看做表 C#
                                                                                  on jHS.stuNo=C#.stuNo)       jHSC        –连接C#和jHS后看做表jHSC

left join
(select stuNo,Score from stuTests where SubjectID=
(select SubjectID from stuSubject where SubjectName=’SQLAdvance’)) SQLAdvance on jHSC.stuNo=SQLAdvance.stuNo     –连接SQLAdvance和jHSC

************************************************

方案三
************************************************

–得到科目号
DECLARE @javaID int,@HTMLID int,@SQLID int,@C#ID int,@SQLAdvanceID int
select @javaID=SubjectID from stuSubject where SubjectName=’Java’
select @HTMLID=SubjectID from stuSubject where SubjectName=’HTML’
select @SQLID=SubjectID from stuSubject where SubjectName=’SQL’
select @C#ID=SubjectID from stuSubject where SubjectName=’C#’
select @SQLAdvanceID=SubjectID from stuSubject where SubjectName=’SQLAdvance’
–实现交叉表
select stuNo,
Java成绩=sum(case subjectID WHEN @javaID then Score else 0 end),
HTML成绩=sum(case subjectID WHEN @HTMLID then Score else 0 end),
SQL成绩=sum(case subjectID WHEN @SQLID then Score else 0 end),
C#成绩=sum(case subjectID WHEN @C#ID then Score else 0 end),
SQLAdvance成绩=sum(case subjectID WHEN @SQLAdvanceID then Score else 0 end)
from stuTests T
group by stuNO

************************************************

初始化数据
************************************************
use master
go
xp_cmdshell ‘mkdir d:\project’, NO_OUTPUT –创建文件夹project,xp_cmdshell为系统存储过程
–检验数据库是否存在，如果为真，删除此数据库–
IF EXISTS(SELECT NAME FROM master.dbo.SYSDATABASES WHERE NAME=N’stuDBTest’)
DROP DATABASE stuDBTest

–创建数据库–
CREATE DATABASE stuDBTest
ON
(NAME=N’stuDB’,
FILENAME=’d:\project\stuDBTest.mdf’,
SIZE=5mb,
MAXSIZE=10mb,
FILEGROWTH=15%)
LOG ON
(NAME=N’stuDB_log’,
FILENAME=’d:\project\stuDBTest_log.ldf’,
SIZE=2mb,
MAXSIZE=4mb,
FILEGROWTH=15%)

USE stuDBTest
GO
SET NOCOUNT ON

–创建学员表stuInfo
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME=N’stuInfo’)
DROP TABLE stuInfo

CREATE TABLE stuInfo
(stuName    NVARCHAR(20)    NOT NULL,
stuNo      NCHAR(6)        NOT NULL,
stuSex     NCHAR(4)        NOT NULL,
stuAge     SMALLINT        NOT NULL,
stuSeat    SMALLINT        IDENTITY(1,1),
stuAddress NTEXT)

ALTER TABLE stuInfo
ADD CONSTRAINT PK_stuNo      PRIMARY KEY(stuNo),
      CONSTRAINT CK_stuNo      CHECK(stuNo LIKE ‘S253[0-9][0-9]‘),
      CONSTRAINT CK_stuSex     CHECK(stuSex=’男’ OR stuSex=’女’),
      CONSTRAINT CK_stuAge     CHECK(stuAge BETWEEN 15 AND 40),
      CONSTRAINT CK_stuSeat    CHECK(stuSeat<=30),
      CONSTRAINT DF_stuAddress DEFAULT (‘地址不详’) FOR stuAddress

–创建科目表
IF EXISTS(SELECT * FROM sysobjects WHERE name=’stuSubject’)
DROP TABLE stuSubject
Go

CREATE TABLE stuSubject — 科目表
(
SubjectID INT IDENTITY (1,1), –ID，自动编号
SubjectName varchar(50) NOT NULL, –科目名称

)
GO

Insert stuSubject(SubjectName) values(‘Java’)
Insert stuSubject(SubjectName) values(‘HTML’)
Insert stuSubject(SubjectName) values(‘SQL’)
Insert stuSubject(SubjectName) values(‘C#’)

Insert stuSubject(SubjectName) values(‘SQLAdvance’)
GO

–创建内测表
IF EXISTS(SELECT * FROM sysobjects WHERE name=’stuTests’)
DROP TABLE stuTests
Go

CREATE TABLE stuTests — 内测表
(
TestID INT IDENTITY (1,1),   –ID，自动编号
stuNo CHAR(6) NOT NULL,   –学号
SubjectID INT   NOT NULL,   –科目ID
Score INT NOT NULL –内测成绩

)
GO

INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)VALUES(‘张秋丽’,'s25301′,’男’,18,’北京海淀’)
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES(‘李斯文’,'s25303′,’女’,22,’河南洛阳’)
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES(‘李文才’,'s25302′,’男’,31)
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES(‘欧阳俊雄’,'s25304′,’男’,28,’新疆威武哈’)

Insert into stuTests(SubjectID, stuNo, Score)
values(1,’s25301′,50)
Insert into stuTests(SubjectID, stuNo, Score)
values(2,’s25301′,60)

Insert into stuTests(SubjectID, stuNo, Score)
values(3,’s25301′,70)
Insert into stuTests(SubjectID, stuNo, Score)
values(4,’s25301′,80)

Insert into stuTests(SubjectID, stuNo, Score)
values(1,’s25302′,100)
Insert into stuTests(SubjectID, stuNo, Score)
values(2,’s25302′,99)

Insert into stuTests(SubjectID, stuNo, Score)
values(3,’s25302′,98)
Insert into stuTests(SubjectID, stuNo, Score)
values(4,’s25302′,97)

Posted in SQL, 学习 | Tagged SQL | Leave a comment

Hello world!

Posted on 2009-08-04 by libra

Welcome to 葵花地。. This is your first post. Edit or delete it, then start blogging!

Posted in 未分类 | 1 Comment

游标的使用(嵌套的游标)

随机数与分页

交叉表

Hello world!

最新日志

最新评论

存档页

分类

功能