一张表没有主键,也没有编号列,我就想按某个字段排序后查指定的行,如第3行.table1:Name Age
Allen 12
bill 18
kate 33
cystal 25
crane 35我要查第2行的数据出来,按Age排序.
我写出一种:
select top 1 * from (select top 2 * from table1 order by age) order by age desc如果table1很大的话,而且是取最后面的几条(select top 2 * from table1 order by age)形成的临时表就会很大,效率就不高了.有哪个大哥有更好的方法,小第只有30分了,不好意思
Allen 12
bill 18
kate 33
cystal 25
crane 35我要查第2行的数据出来,按Age排序.
我写出一种:
select top 1 * from (select top 2 * from table1 order by age) order by age desc如果table1很大的话,而且是取最后面的几条(select top 2 * from table1 order by age)形成的临时表就会很大,效率就不高了.有哪个大哥有更好的方法,小第只有30分了,不好意思
select *
from
(select *,row=row_number()over(order by age asc) from ta)ta
where row=22000:
--取最后两条,可生成临时表取其中一条
set rowcount 2
select * from ta order by age desc
set rowcount0
----创建测试数据
declare @t table(Name varchar(10),Age int)
insert @t
select 'Allen', 12 union all
select 'bill', 18 union all
select 'kate', 33 union all
select 'cystal', 25 union all
select 'crane', 35----查询(取Age第二小的行)
SELECT * FROM @t as a WHERE (select count(*) from @t where Age < a.Age) = 1/*结果
Name Age
------------------
bill 18
*/
insert table1
select 'Allen',12
union select 'bill',18
union select 'kate',33
union select 'cystal',25
union select 'crane',35
select id=identity(int,1,1),* into # from table1select * from # where id between 2 and 3drop table table1,#
/* 结果 id Name Age
----------- ---------- -----------
2 bill 18
3 crane 35(所影响的行数为 2 行)
*/