CREATE TABLE TEST(ID INT) INSERT INTO TEST SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 GOselect top 5 * from test where id not in (select top 5 id from test)DROP TABLE TESTID ----------- 6 7 8 9 10(所影响的行数为 5 行)
select * from (select top 5 * from table order by [ID] desc ) a order by [id]再嵌套一层就可以了。
select * from (select top 5 * from table order by [ID] desc ) a order by [id]
select * from (select top 5 * from table order by [ID] desc ) a order by [id] asc先选出符合的,你已经做到,再排序,即order by id asc
我想查最后几行数据 select top 5 * from table order by [ID] desc 此语句可以,但是最后1行的数据变成5行数据里的第1行了,怎么办啊,我希望最后1行数据还是在最后1行select top 5 * from ( select top 5 * from table order by [ID] desc ) a order by id
if object_id('tb') is not null drop table tb create table tb ( id int identity(1,1) primary key, ppp char(1) ) insert tb select 'a' union all select 'b' union all select 'c' union all select 'd' union all select 'e' union all select 'f' union all select 'g' union all select 'h' union all select 'i' union all select 'j' union all select 'k'select * from tb select top 5 * from(select top 5 * from tb order by id desc) a order by [id] asc 7 g 8 h 9 i 10 j 11 k
把结果再排一次不就可以了。 select * from (select top 5 * from tb order by [ID] desc ) a order by [ID]
SELECT * FROM ( SELECT TOP 5 * FROM TABLE ORDER BY [ID] DESC ) A ORDER BY [ID]
if exists(select name from sys.objects where name='TEST') drop table TEST CREATE TABLE TEST(ID INT) INSERT INTO TEST SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL select 11 union all select 12 Union all select 21 union all select 13select * from test where id not in (select top ((select count(id)from TEST)-5) id from test) /*结果 id 10 11 12 21 13 */
CREATE TABLE TEST(ID INT)
INSERT INTO TEST SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10
GOselect top 5 * from test where id not in (select top 5 id from test)DROP TABLE TESTID
-----------
6
7
8
9
10(所影响的行数为 5 行)
select *
from (select top 5 * from table order by [ID] desc ) a
order by [id]再嵌套一层就可以了。
select top 5 * from table order by [ID] desc 此语句可以,但是最后1行的数据变成5行数据里的第1行了,怎么办啊,我希望最后1行数据还是在最后1行select top 5 * from (
select top 5 * from table order by [ID] desc ) a order by id
if object_id('tb') is not null
drop table tb
create table tb
(
id int identity(1,1) primary key,
ppp char(1)
)
insert tb select 'a'
union all select 'b'
union all select 'c'
union all select 'd'
union all select 'e'
union all select 'f'
union all select 'g'
union all select 'h'
union all select 'i'
union all select 'j'
union all select 'k'select * from tb
select top 5 * from(select top 5 * from tb order by id desc) a
order by [id] asc
7
g 8
h 9
i 10
j 11
k
select * from (select top 5 * from tb order by [ID] desc ) a order by [ID]
FROM (
SELECT TOP 5 *
FROM TABLE
ORDER BY [ID] DESC
) A
ORDER BY [ID]
drop table TEST
CREATE TABLE TEST(ID INT)
INSERT INTO TEST SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
select 11 union all
select 12 Union all
select 21 union all
select 13select *
from test
where id not in (select top ((select count(id)from TEST)-5) id from test)
/*结果
id
10
11
12
21
13
*/