1、在inner join后,如何分页。
sqlserver 对一张表进行分页查询,但是还要通过第二张表获取信息:
--比如有一张表
create table Student
(
sid int primary key identity(1,1) ,
sname varchar(15) not null
)
--第二张表
create table Comment
(
id int primary key identity(1,1) ,
sid int not null
)
我需要对第二张表comment 进行分页查询,但是还要通过第一张表查询姓名
要改如何写sql代码。
我用内连接后接下来该如何做。
解法:
declare @page_size int;
declare @page_num int;
--比如:每页10条记录
set @page_size = 10;
--比如:先取第1页
set @page_num = 1;
select id,sid,sname
from
(
select c.id,
c.sid,
s.sname,
--这里按照@@servername来排序,
--你可以根据需要按照id,sid,sname等字段来排序
(row_number() over(order by @@servername) - 1) / @page_size as rownum
from commet c
inner join student s
on c.sid = s.sid
)t
where rownum = @page_num - 1
由于没有实验数据,下面通过sys.objects来实现:
declare @page_size int;
declare @page_num int;
--比如:每页10条记录
set @page_size = 10;
--比如:先取第1页
set @page_num = 1;
select *
from
(
select *,
row_number() over(order by @@servername) as rownum,
--这里按照@@servername来排序,
--你可以根据需要按照id,sid,sname等字段来排序
(row_number() over(order by @@servername) - 1) / @page_size as pagenum
from sys.objects
)t
where pagenum = @page_num - 1
2、group by trueName后,返回不尽相同的userName,也就是userName字段的值需要一定的随机性,然后求score的总和。
解法:
if object_id('tb') is not null drop table tb
go
create table tb([id] int,[trueName] varchar(4),[userName] varchar(3),[score] int)
insert tb
select 1,'张三','zs',80 union all
select 2,'张三','zs1',100 union all
select 3,'张三','zs2',90 union all
select 1,'李四','zs',80 union all
select 2,'李四','zs1',100 union all
select 3,'李四','zs2',90
--第1次运行
select *
from
(
select truename,
username,
row_number() over(partition by truename order by checksum(newid())) as rownum,
sum(score) over(partition by truename) as score
from tb
)a
where a.rownum = 1
/*
truename username rownum score
-------- -------- -------------------- -----------
李四 zs1 1 270
张三 zs2 1 270
*/
--第2次运行
/*
truename username rownum score
-------- -------- -------------------- -----------
李四 zs2 1 270
张三 zs 1 270
*/
3、同一表两条记录同一字段做比较的问题
一张sql表中的最新两条记录里的两个数字类型字段对比,最后一条比上一条的值大则输出上升,一样大输出持平 比上一条小则输出下降 这个数据查询怎么写?
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Id] int,[Name] varchar(3),[Result] int,[Date] datetime)
insert [tb]
select 1,'001',90,'2013-11-10' union all
select 2,'002',85,'2013-11-10' union all
select 3,'003',87,'2013-11-10' union all
select 4,'001',95,'2013-11-15' union all
select 5,'002',83,'2013-11-15' union all
select 6,'003',89,'2013-11-15' union all
select 7,'001',92,'2013-11-20' union ALL
select 8,'002',83,'2013-11-20' union all
select 9,'003',88,'2013-11-20'
go
;with t
as
(
select *,
ROW_NUMBER() over(partition by [Name] order by [Date] desc) rownum
from tb
)
select t1.Name,t1.Result,
case when t1.[Result] > t2.[Result] then '上升'
when t1.[Result] = t2.[Result] then '持平'
when t1.[Result] < t2.[Result] then '下降'
end flag
from t t1
left join t t2
on t1.Name = t2.Name and t1.rownum = t2.rownum - 1
and t2.rownum = 2
where t1.rownum = 1
/*
Name Result flag
001 92 下降
002 83 持平
003 88 下降
*/