来个高手帮我解决一下这问题 sql 分页取每页最后一条

[eyuannet]

sql 分页取每页最后一条

table1

id name ...
1 s
2 c
3 b
4 d
6 s
8 t
9 a
10 cc
11 tt
13 ss

14 e
15 b
16 u
17 9
18 jj
19 pp
20 to
21 tb
22 tu

23 uc
24 ue
25 ub
26 yk
.........

id是有序的，但不是连续的，现在分页，每页取10条，按id排序，想得到每一中最大的id结果如下：

table1
13
22
....

[q465897859]

去当前分页的 最大id 就能取出了

[昵称被占用了]

;WITH CTE AS (
SELECT * ,ROW_NUMBER() OVER (ORDER BY ID) AS NUM
FROM TABLE1
WHERE ...
)
SELECT ID FROM CTE
WHERE NUM % 10 = 0

[快溜]

select * from
(select no=row_number(order by id),* from tb)t
where no%10=0

[昵称被占用了]

每页最后一条就是连续排的第10 ，20 ，...
转换下思路就可以用取模来获取了

[westlwt]

[Quote=引用 2 楼 haiwer 的回复:]

SQL code
;WITH CTE AS (
SELECT * ,ROW_NUMBER() OVER (ORDER BY ID) AS NUM
FROM TABLE1
WHERE ...
)
SELECT ID FROM CTE
WHERE NUM % 10 = 0
[/Quote]
应该挺靠谱,支持

[eyuannet]

大侠
最后不足10条的那页怎么取。。
[Quote=引用 2 楼 haiwer 的回复:]
SQL code

;WITH CTE AS (
SELECT * ,ROW_NUMBER() OVER (ORDER BY ID) AS NUM
FROM TABLE1
WHERE ...
)
SELECT ID FROM CTE
WHERE NUM % 10 = 0
[/Quote]

[--小F--]

用CTE排序 除10余数为0的就满足要求了

select px=identity(int,1,1),* into #t from tb

select * from #t where px%10=0

[niit_yi]

select max(id) from (select ROW_NUMBER()OVER(ORDER BY lsh id) rowNum,* from table ) as a where a.rowNum>@rowNum and a.rowNum<@rowNum

这个语句是对原来的表格里面的数据进行按照id排序，再重新编号，这样编号的rowNum是连续的，<@rowNum

是查询行号的条件！

[昵称被占用了]

[Quote=引用 6 楼 eyuannet 的回复:]
大侠
最后不足10条的那页怎么取。。

引用 2 楼 haiwer 的回复:
SQL code

;WITH CTE AS (
SELECT * ,ROW_NUMBER() OVER (ORDER BY ID) AS NUM
FROM TABLE1
WHERE ...
)
SELECT ID FROM CTE
WHERE NUM % 10 = 0
[/Quote]

;WITH CTE AS (
SELECT * ,ROW_NUMBER() OVER (ORDER BY ID) AS NUM
FROM TABLE1
WHERE ...
)
SELECT ID FROM CTE A
WHERE NUM % 10 = 0
OR NOT EXISTS (
SELECT 1 FROM CTE WHERE NUM > A.NUM
)

[快溜]

[Quote=引用 6 楼 eyuannet 的回复:]
大侠
最后不足10条的那页怎么取。。

引用 2 楼 haiwer 的回复:
SQL code

;WITH CTE AS (
SELECT * ,ROW_NUMBER() OVER (ORDER BY ID) AS NUM
FROM TABLE1
WHERE ...
)
SELECT ID FROM CTE
WHERE NUM % 10 = 0
[/Quote]

with cte as
(select no=row_number(order by id),* from tb)
select * from cte where no%10=0
union
select top 1 * from cte order by no desc

[--小F--]

select px=identity(int,1,1),* into #t from tb

select * from #t where px%10=0 or not exists(select 1 from #t where px>t.px)

[布谷歌]

drop table #table1
create table #table1(id int, name varchar(20))

insert #table1 values(1, 's')
insert #table1 values(2, 'c')
insert #table1 values(3, 'b')
insert #table1 values(4, 'd')
insert #table1 values(6, 's')
insert #table1 values(8, 't')
insert #table1 values(9, 'a')
insert #table1 values(10, 'cc')
insert #table1 values(11, 'tt')
insert #table1 values(13, 'ss')

insert #table1 values(14, 'e')
insert #table1 values(15, 'b')
insert #table1 values(16, 'u')
insert #table1 values(17, '9')
insert #table1 values(18, 'jj')
insert #table1 values(19, 'pp')
insert #table1 values(20, 'to')
insert #table1 values(21, 'tb')
insert #table1 values(22, 'tu')
insert #table1 values(23, 'uc')

insert #table1 values(24, 'ue')
insert #table1 values(25, 'ub')
insert #table1 values(26, 'yk')

drop table #
select (ROW_NUMBER() over(order by id)-1)/10 as rowNo, id, name into # from #table1
select * from # a where id=(select max(id) from # where rowNo=a.rowNo)