select * into tb from (
select 1 id ,'AS' name
union all
select 11,'AS'
union all
select 2,'AS'
union all
select 333,'AS'
union all
select 4,'AS'
union all
select 5,'AS'
union all
select 6,'AS'
union all
select 66,'AS'
union all
select 77,'AS'
union all
select 88,'AS'
union all
select 99,'AS'
union all
select 1,'AS'
union all
select 111,'AS' )a
先从小到大排离,排序后的前十条数据从大到小排序
select * from (
select top 10 * from tb order by id asc ) t order by id desc;
select 1 id ,'AS' name
union all
select 11,'AS'
union all
select 2,'AS'
union all
select 333,'AS'
union all
select 4,'AS'
union all
select 5,'AS'
union all
select 6,'AS'
union all
select 66,'AS'
union all
select 77,'AS'
union all
select 88,'AS'
union all
select 99,'AS'
union all
select 1,'AS'
union all
select 111,'AS' )a
SELECT *
FROM tb AS A
ORDER BY
CASE WHEN ROW_NUMBER() OVER(ORDER BY id) <= 10 THEN 0 ELSE 1 END,
CASE WHEN ROW_NUMBER() OVER(ORDER BY id) <= 10 THEN -id ELSE id ENDDROP TABLE tb/*
id name
----------- ----
88 AS
77 AS
66 AS
11 AS
6 AS
5 AS
4 AS
2 AS
1 AS
1 AS
99 AS
111 AS
333 AS(13 行受影响)*/
if object_id('tb') is not null drop table tb
go
create table tb(id int,name varchar(10))
insert tb select
1 ,'AS'
union all
select 11,'AS'
union all
select 2,'AS'
union all
select 333,'AS'
union all
select 4,'AS'
union all
select 5,'AS'
union all
select 6,'AS'
union all
select 66,'AS'
union all
select 77,'AS'
union all
select 88,'AS'
union all
select 99,'AS'
union all
select 1,'AS'
union all
select 111,'AS'
select *,id1=identity(int,1,1) into #t from tb order by id ascselect id,name from #t order by case when id1<=10 then id1 else 0 end desc,id ascid name
----------- ----------
88 AS
77 AS
66 AS
11 AS
6 AS
5 AS
4 AS
2 AS
1 AS
1 AS
99 AS
111 AS
333 AS(所影响的行数为 13 行)drop table #t
from
(select *,px=(select count(1)+1 from tb where id<t.id) from tb t) tt
where
px between 1 and 10
order by
id desc
select * from (select top 10 * from tb order by id)t order by id desc
union all
select * from tb where id not in(select top 10 id from tb order by id) order by id
from
(select *,px=(select count(1)+1 from tb where id<t.id) from tb t) tt
order by
case when px between 1 and 10 then 1 else 0 end desc,
id
假如id列是数字,咋办? 第一个case when 有什么用?
select 1 id ,'AS' name
union all
select 11,'AS'
union all
select 2,'AS'
union all
select 333,'AS'
union all
select 4,'AS'
union all
select 5,'AS'
union all
select 6,'AS'
union all
select 66,'AS'
union all
select 77,'AS'
union all
select 88,'AS'
union all
select 99,'AS'
union all
select 1,'AS'
union all
select 111,'AS' )aselect id0=identity (int),* into # from tbselect id,name from #
order by
case when id0 between 1 and 10 then id0 else 0 end desc , id asc
drop table tb
drop table #/*id name
----------- ----
88 AS
77 AS
66 AS
6 AS
5 AS
4 AS
333 AS
2 AS
11 AS
1 AS
1 AS
99 AS
111 AS(13 行受影响)
*/
select id,name
from
(select *,px=(select count(1)+1 from tb where id<t.id) from tb t) tt
order by
case when px between 1 and 10 then id else 0 end desc,
id
/**
id name
----------- ----
88 AS
77 AS
66 AS
11 AS
6 AS
5 AS
4 AS
2 AS
1 AS
1 AS
99 AS
111 AS
333 AS(13 行受影响)
**/
/*这才是要返回的结果,谢谢四方城,id列是字符,举例的时候没举清楚,不好意思
333 AS
111 AS
66 AS
11 AS
6 AS
5 AS
4 AS
2 AS
1 AS
1 AS
77 AS
88 AS
99 AS
*/
总结一下order by -字段:为按字段降序排列
order by 字段:为按字段升序排序
通过case来判断分割点实现效果