sql sever 子查询和分页

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、子查询

子查询就是把一个查询作为另一个查询的一部分进行一个组合

举例说明：

现有三张表
账号信息表、银行卡表、交易信息表

create table AccountInfo --账户信息表
(
   AccountId int primary key identity(1,1),--账户编号
   AccountCode varchar(20) not null,--身份证号码
   AccountPhone varchar(20) not null,--电话号码
   RealName varchar(20) not null,--真实姓名
   OpenTime smalldatetime not null--开户时间
)

create table BankCard --银行卡
(
   CardNo varchar(20) primary key ,--银行卡号
   AccountId int not null,--账户编号（与账户信息表形成主外键关系）
   CardPwd varchar(30) not null,--银行卡密码
   CardMoney money not null,--银行卡余额
   CardState int not null--状态：1正常、2挂失、3冻结、4注销
)

create table CardExchange --交易信息表(存储存钱和取钱的记录)
(
  ExchangeId int primary key identity(1,1),
  CardNo varchar(30) not null,--银行卡号
  MoneyInBank money not null,--存钱金额
  MoneyOutBank money not null,--取钱金额
  ExchangeTime smalldatetime not null--交易时间
)

示例1：已知张三卡号为241532131，查询出余额比张三多的银行卡号，显示卡号、身份证、姓名、余额

--方案1：不用子查询
declare @balance money
select @balance=(select CardMoney from BankCard where CardNo='241532131')

select CardNo 卡号,AccountCode 身份证,RealName 姓名,CardMoney 余额 from BankCard
inner join AccountInfo on BankCard.AccountId=AccountInfo.AccountId
where CardMoney>@balance

--方案2：用子查询
select CardNo 卡号,AccountCode 身份证,RealName 姓名,CardMoney 余额 from BankCard
inner join AccountInfo on BankCard.AccountId=AccountInfo.AccountId
where CardMoney>(select CardMoney from BankCard where CardNo='241532131')
--把张三的余额作为一个子查询（把这个查询作为当前查询的条件）

示例2：从所有账户信息表中查询出余额最高的交易明细（存钱取钱信息）

select * from CardExchange where CardNo=
(select top 1 CardNo from BankCard order by CardMoney desc)
--找出余额最高的银行卡号

事实上，上面这种方法，如果余额最高有两个人（并列第一），只能查出一个
改进思路如下：

select * from CardExchange where 余额最高的人的卡号

--余额最高人的卡号
select * from BankCard where CardMoney =余额最大值

--余额最大值
select max(CardMoney) from BankCard

具体代码：

select * from BankCard where CardMoney in--第三步查出该卡号的交易明细
(select * from BankCard where CardMoney= --第二步查出余额最大的人的银行卡号
(select max(CardMoney) from BankCard)--第一步先查出余额最大的（数据如果有多个，就会显示多条数据）
)

二、分页

比如现在有这样一张表

create table Student
(
   StuId int primary key identity(1,2),--自动编号，从1开始，步长为2
   StuName varchar(10),
   StuSex varchar(4)
)


insert into Student(StuName,StuSex) values('刘备','男')
insert into Student(StuName,StuSex) values('关羽','男')
insert into Student(StuName,StuSex) values('张飞','男')
insert into Student(StuName,StuSex) values('曹操','男')
insert into Student(StuName,StuSex) values('张辽','男')
insert into Student(StuName,StuSex) values('荀彧','男')
insert into Student(StuName,StuSex) values('魏延','男')
insert into Student(StuName,StuSex) values('诸葛亮','男')
insert into Student(StuName,StuSex) values('周瑜','男')
insert into Student(StuName,StuSex) values('孙策','男')
insert into Student(StuName,StuSex) values('孙权','男')
insert into Student(StuName,StuSex) values('貂蝉','女')
insert into Student(StuName,StuSex) values('大乔','女')
insert into Student(StuName,StuSex) values('小乔','女')
insert into Student(StuName,StuSex) values('郭嘉','男')
insert into Student(StuName,StuSex) values('太史慈','男')
insert into Student(StuName,StuSex) values('袁绍','男')
insert into Student(StuName,StuSex) values('袁术','男')
insert into Student(StuName,StuSex) values('孙坚','男')

select * from Student

表里面现有19条数据

现在对其进行分页
示例：假设每页5条数据
分页方案1：使用top进行分页

--查询第一页
select top 5 * from Student

--查询第二页
select top 5 * from Student
where StuId not in(select top 5 StuId from Student)

--查询第三页
select top 5 * from Student
where StuId not in(select top 10 StuId from Student)

--查询第四页
select top 5 * from Student
where StuId not in(select top 15 StuId from Student)

那通过上面的代码，我们就发现了一个分页的规律：

select top 页码大小 * from Student
where StuId not in(select top 页码大小*(当前页数-1) StuId from Student)

具体分页代码：

declare @PageSize int =5
declare @PageIndex int =1
select top(@PageSize)* from Student
where StuId not in (select top(@PageSize*(@PageIndex-1)) StuId from Student)

分页方案2：使用row_number进行分页

我们通过ROW_NUMBER()来获取每行的行号

select ROW_NUMBER() over(order by StuId)RowId  from Student
--RowId是起别名

获得行号后，再搭配*就可以查询出有行号的表数据

select ROW_NUMBER() over(order by StuId)RowId,*  from Student

思路：

select * from
(select ROW_NUMBER() over(order by StuId)RowId,*  from Student ) tmp--记为tmp表
where RowId between (当前页-1)*页码大小+1 and 当前页*页码大小

具体代码：

--查询第一页
declare @PageSize int =5
declare @PageIndex int =1
select * from
(select ROW_NUMBER() over(order by StuId)RowId,*  from Student ) tmp--记为tmp表
where RowId between (@PageIndex-1)*@PageSize+1 and @PageIndex*@PageSize

后面要改查询第n页，就把@PageIndex值改成n即可
比如查询第2页

declare @PageSize int =5
declare @PageIndex int =2
select * from
(select ROW_NUMBER() over(order by StuId)RowId,*  from Student ) tmp--记为tmp表
where RowId between (@PageIndex-1)*@PageSize+1 and @PageIndex*@PageSize