create database Exam_Week3

GO

USE Exam_Week3

GO

create table Classs

(

ClaID int identity(1001,1),

ClassName varchar(100),

Counts int

)

go

insert into Classs values

('1706B',5),

('1706A',5),

('1705B',5),

('1705A',5),

('1704B',5)

go

create table Student

(

SID int identity(2001,1),

ClaID int, --班级id

Sname varchar(100), --学生姓名

Sage datetime,

Ssex bit

)

go

insert into Student values

(1001,'张三1',2013-10-02,1),

(1001,'张三2',2013-10-02,1),

(1001,'张三3',2013-10-02,1),

(1001,'张三4',2013-10-02,1),

(1001,'张三5',2013-10-02,1),

(1002,'李四1',2013-10-02,1),

(1002,'李四2',2013-10-02,1),

(1002,'李四3',2013-10-02,1),

(1002,'李四4',2013-10-02,1),

(1002,'李四5',2013-10-02,1),

(1003,'李艳1',2013-10-02,0),

(1003,'李艳2',2013-10-02,0),

(1003,'李艳3',2013-10-02,0),

(1003,'李艳4',2013-10-02,0),

(1003,'李艳5',2013-10-02,0),

(1004,'王五1',2013-10-02,1),

(1004,'王五2',2013-10-02,1),

(1004,'王五3',2013-10-02,1),

(1004,'王五4',2013-10-02,1),

(1004,'王五5',2013-10-02,1),

(1005,'李流1',2013-10-02,1),

(1005,'李流2',2013-10-02,1),

(1005,'李流3',2013-10-02,1),

(1005,'李流4',2013-10-02,1),

(1005,'李流5',2013-10-02,1),

(1005,'李流6',2013-10-02,1)

go

create table Course

(

CID int identity(3001,1),

Cname varchar(100),

TID int --教室编号

)

go

insert into Course values

('C#',4001),

('php',4002),

('Ajax',4003),

('Webseave',4004),

('网站',4005)

go

create table Teacher

(

TID int identity(4001,1),

Tname varchar(100),

)

go

insert into Teacher values

('王红'),

('陆兵'),

('霍建华'),

('李冰冰'),

('宋小宝')

go

create table SC

(

scid int identity(5001,1),

SID int,

CID int, --课程编号

score int

)

go

insert into SC values

(2001,3001,93),

(2001,3002,85),

(2001,3003,57),

(2001,3004,98),

(2001,3005,99),

(2002,3001,80),

(2002,3002,100),

(2002,3003,55),

(2002,3004,77),

(2002,3005,43),

(2003,3001,85),

(2003,3002,86),

(2003,3003,88),

(2003,3004,90),

(2003,3005,91),

(2004,3001,79),

(2004,3002,80),

(2004,3003,64),

(2004,3004,63),

(2004,3005,62),

(2005,3001,74),

(2005,3002,73),

(2005,3003,96),

(2005,3004,89),

(2005,3005,92),

(2005,3001,92)

select * from

(

select Classs.ClaID,Classs.ClassName,Classs.Counts,Student.Sage,Student.SID,Student.Sname,Student.Ssex,

SC.scid,SC.score,Course.CID,Course.Cname,Teacher.TID,Teacher.Tname,ROW_NUMBER() over(order by score) as rid

from Classs

join Student on Classs.ClaID=Student.ClaID

join SC on sc.SID=Student.SID

join Course on Course.CID=sc.CID

join Teacher on Teacher.TID=Course.TID

) as temp

where rid>

-----分页存储过程

--判断存储过程是否存在

if OBJECT_ID('proc_Page') is not null

--删除存储过程

drop proc proc_Page

go

--创建存储过程

create proc proc_Page

--参数

@ClaId int=0, --班级Id

@Sage datetime=null, --出生日期

@Sname nvarchar(10)=null, --学生名称

@SmallScore int=0, --小成绩

@BigScore int=0, --大成绩

@CID int=0, --课程Id

@Tname nvarchar(10)=null, --讲师名字

@PageIndex int,    --当前页

@PageSize int,    --每页条数

@TotalCount int out,    --总条数

@order int=1 --1升序 0降序

as

--变量

declare

@sql varchar(max), --总sql

@sqlWhere varchar(max), --条件

@rid int, --分页条件

@countSql nvarchar(max) --个数sql

set @sql='';

set @sqlWhere=' where 1=1';

set @rid=(@PageIndex-1)*@PageSize;

set @countSql='select @total=count(1)

from Classs

join Student on Classs.ClaID=Student.ClaID

join SC on sc.SID=Student.SID

join Course on Course.CID=sc.CID

join Teacher on Teacher.TID=Course.TID';

--条件班级Id

if @ClaId!=0

begin

set @sqlWhere+=' and Classs.ClaId='+str(@ClaId);

end

----出生日期

if @Sage is not null

begin

set @sqlWhere+=' and Sage='+@Sage;

end

--学生名称

if @Sname is not null

begin

set @sqlWhere+=' and Sname like ''%'+@Sname+'%''';

end

--小成绩

if @SmallScore !=0

begin

set @sqlWhere+=' and Score>='+STR(@SmallScore);

end

--大成绩

if @BigScore !=0

begin

set @sqlWhere+=' and Score<='+STR(@BigScore);

end

--课程Id

if @CID!=0

begin

set @sqlWhere+=' and CID='+str(@CID);

end

--讲师名字

if @Tname is not null

begin

set @sqlWhere+=' and Tname like ''%'+@Tname+'%''';

end

--总条数

set @countSql+=@sqlWhere;

--执行计算条数的sql语句并且给输出参数赋值

exec sp_executesql @countSql, N'@total int out',@total=@TotalCount out

--排序

declare @o varchar(4);

if @order=1

begin

set @o='asc';

end

if @order=0

begin

set @o='desc';

end

--总sql

set @sql='select top '+str(@PageSize)+' * from

(

select Classs.ClaID,Classs.ClassName,Classs.Counts,Student.Sage,Student.SID,Student.Sname,Student.Ssex,

SC.scid,SC.score,Course.CID,Course.Cname,Teacher.TID,Teacher.Tname,ROW_NUMBER() over(order by score '+@o+') as rid

from Classs

join Student on Classs.ClaID=Student.ClaID

join SC on sc.SID=Student.SID

join Course on Course.CID=sc.CID

join Teacher on Teacher.TID=Course.TID';

--条件

set @sql+=@sqlWhere;

set @sql+=' ) as temp

where rid>'+STR(@rid);

exec (@sql);