USE AdventureWorks;
GO
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;
select * from (
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader) as d
where rownumber between 11 and 20
create proc up_showPage1
(
@pageSize int,
@pageIndex int
)
as
begin
select * from (
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader) as d
where rownumber between @pageSize*(@pageIndex-1)+1 and @pageSize*@pageindex
end
up_showPage1 5,1
select top 5 * from sales.salesOrderHeader
/************************************************************
存储过程: up_commonTurnPage
功能描述: 通用翻页
输入参数:
@tableName: 表名或视图名称,必须
@fieldList: 字段列表,默认值为 *
@orderRule: 排序规则,必须,如 order by orderDate desc
@pageSize: 每页行数,如 10
@pageIndex: 当前页码,如 1
@queryWhere:查询每件,不能加where,如 where name like '%sun%'
返 回 值: 无
测试范例:exec up_commonshowpage 'authors','au_lname,phone,address',
'order by au_id',5,2,'au_lname like ''%s%'''
************************************************************/
CREATE PROCEDURE [dbo].[up_commonShowPage]
@tableName varchar(255), -- 表名或视图表
@fieldList varchar(1000) = '*', -- 字段列表
@orderRule varchar(255)='', -- 排序规则
@pageSize int = 10, -- 每页行数
@pageIndex int = 1, -- 当前页码
@queryWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
begin
declare @strSQL varchar(1000)
if @queryWhere !=''
set @queryWhere=' where '+@queryWhere
set @strSQL='
SELECT * FROM
(SELECT ROW_NUMBER() OVER ('+@orderRule+' ) AS rowNumber,
'+@fieldList+' FROM ['+@tableName+']'+@queryWhere+') AS NewTable
WHERE rowNumber BETWEEN '+str((@pageIndex-1)*@pageSize+1)+' AND '+str(@pageIndex*@pageSize)
exec (@strSQL)
end
select * from
(SELECT ROW_NUMBER() OVER (order by id) AS rowNumber,id,name,sex,title,departmentname
from view_employee_department where name like '%金%') as newtable
where rowNumber between 1 and 5
exec up_commonshowpage 'view_employee_department','id,name,sex,title,departmentname',
'order by id',5,4,'name like ''%黄%'''
select * from view_employee_department where name like '%黄%'
alter PROCEDURE up_commonShowPage
@tableName varchar(255), -- 表名或视图表
@fieldList varchar(1000) = '*', -- 字段列表
@orderRule varchar(255)='', -- 排序规则
@pageSize int = 10, -- 每页行数
@pageIndex int = 1, -- 当前页码
@queryWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
begin
declare @strSQL varchar(1000)
if @queryWhere !=''
set @queryWhere=' where '+@queryWhere
set @strSQL='
SELECT * FROM
(SELECT ROW_NUMBER() OVER ('+@orderRule+' ) AS rowNumber,'+@fieldList+' FROM ['+@tableName+']'+@queryWhere+') AS NewTable
WHERE rowNumber BETWEEN '+str((@pageIndex-1)*@pageSize+1)+' AND '+str(@pageIndex*@pageSize)
exec (@strSQL)
end
-----------------------------------------------------------------------------------------
求满足条件行数的存储过程
create proc up_commonRecord
declare @s varchar(200)
declare @count int
set @s='select count(*) from employees'
execute(set @count=@s)
select @count
declare @totalRecords int
exec up_commonshowpage 'employees','id,phone,name','order by id',5,2,'name like ''%%'''
select * from authors
select * from authors
CREATE VIEW [dbo].[view_Employee_Department]
AS
SELECT dbo.Employees.id, dbo.Employees.name, dbo.Employees.sex, dbo.Employees.birthday, dbo.Employees.title, dbo.Employees.departmentId,
dbo.Employees.phone, dbo.Employees.photo, dbo.Employees.Description, dbo.Department.Name AS DepartmentName
FROM dbo.Employees INNER JOIN
dbo.Department ON dbo.Employees.departmentId = dbo.Department.id
select * from view_employee_department
truncate table department
CREATE TABLE Department(
[id] [int] IDENTITY(1,1) NOT NULL primary key, --编号
[Name] [nvarchar](10) NOT NULL, --名称
[phone] [varchar](15) NULL, --电话
[leader] [nvarchar](10) NULL, --负责人
[Description] [nvarchar](100) NULL, --职能
)
drop table Employees
create table Employees(
id int identity not null primary key,
name nvarchar(10) not null, --姓名
sex nchar(1) not null check(sex in('男','女')),
birthday smalldatetime check(birthday <getdate()),
title nvarchar(10),--职务
departmentId int references Department(id),--部门编号
phone varchar(50),
photo varchar(60),
Description nvarchar(200)
)
insert into department values('一部','12345678','赵','一部')
insert into department values('二部','12345678','钱','二部')
insert into department values('三部','12345678','孙','三部')
insert into department values('四部','12345678','李','四部')
insert into department values('五部','12345678','周','五部')
insert into employees values('赵一','男','1985-1-1','销售员',1,'111111111','photo/boy.gif','')
insert into employees values('赵二','男','1985-1-1','销售员',1,'111111111','photo/boy.gif','')
insert into employees values('赵三','男','1985-1-1','销售员',1,'111111111','photo/boy.gif','')
insert into employees values('赵四','男','1985-1-1','销售员',1,'111111111','photo/boy.gif','')
insert into employees values('赵五','男','1985-1-1','销售员',1,'111111111','photo/boy.gif','')
/************************************************************
存储过程: up_insertEmployee
功能描述: 注册新员工
输入参数:
@name:员工姓名
@sex: 性别
@birthday: 生日
@title: 职务
@departmentId: 部门编号
@phone: 电话
@photo: 像片
@description: 描述
返 回 值: 无
************************************************************/
alter PROCEDURE up_insertEmployee
@name varchar(10),
@sex nchar(1),
@birthday smalldatetime,
@title nvarchar(10),
@departmentId int,
@phone varchar(50),
@photo varchar(60),
@description nvarchar(200)
AS
begin
insert into employees values(@name,@sex,@birthday,@title,@departmentId,@phone,@photo,@description)
end
select * from employees
/*******************************
根据员工编号到视图中查询员工
*******************************/
alter proc up_getEmployeeById
(
@id int
)
as
begin
select * from view_employee_department where id=@id
end
up_getEmployeeById 1