100万数据，选第20条到41条。ORACLE和SQL SERVER各实现一次

[ZHENNIUBILE]

不是作业，是面试。没做出来。

[obuntu]

oracle:

select * from tb where rownum between 20 and 41

sql server:

with a
(
select rownumber rn,* from tb
)
select * from a where rn between 20 and 41


应该是这样吧。

[bancxc]

[Quote=引用 1 楼 obuntu 的回复:]
oracle:

select * from tb where rownum between 20 and 41

sql server:

with a
(
select rownumber rn,* from tb
)
select * from a where rn between 20 and 41


应该是这样吧。
[/Quote]膜拜》

[claro]

--补充obuntu
--MSSQL
with a
(
  select rownumber = ROW_NUMBER() OVER (ORDER BY getdate()) ,* from tb
)
select * 
from a 
where rownumber between 20 and 41

题目不严谨，忽略排序。

[obuntu]

[Quote=引用 3 楼 claro 的回复:]

SQL code
--补充obuntu
--MSSQL
with a
(
select rownumber = ROW_NUMBER() OVER (ORDER BY getdate()) ,* from tb
)
select *
from a
where rownumber between 20 and 41



题目不严谨，忽略排序。
[/Quote]



很少写SQL ,手生的很。。

[budong0000]

with a
(
select rownumber rn,* from tb
)
select * from a where rn between 20 and 41
sql 里下面这个更快吧

with a
(
select top 41 rownumber() over(order by col) rn,* from tb
)
select * from a where rn between 20 and 41

[claro]

[Quote=引用 5 楼 budong0000 的回复:]

with a
(
select rownumber rn,* from tb
)
select * from a where rn between 20 and 41
sql 里下面这个更快吧

with a
(
select top 41 rownumber() over(order by col) rn,* from tb
)
select * from a ……
[/Quote] 对。很好。

如果题目有要求性能，我会建议它在新建的自增列上做个非聚集索引。