select * from (select top 50 * from (select top 100 * from MyTabl ORDER BY field1) as a ORDER BY field1 desc) as b ORDER BY field1
select top 50 * from (select top 100 * from MyTabl ORDER BY field1) ss order by field1 desc;
nononono(null,null)多一次查詢,不過是正序.你們兩個可真快啊,佩服!佩服!
select top 50 * from mytab1 where field1 in (select top 100 field1 from mytab1 order by field1) order by field1 desc
不可能吧nononono(null,null)的做法是對的啊,你加上最後的order by field1了嗎
to jasmine(Yaya) : select * from (select top 30 * from (select top 100 * from eve_log as c order by eve_log_id) as a order by eve_log_id desc) as b order by eve_log_id asc没错吧,就是倒不过来
select top 100 * from eve_log as c order by eve_log_id 的排序没加asc或desc,就是采用默认设置
沒有道理啊,我在MSsql2000測過了是對的啊
我知道了,我在sqlserver7.0上測了一下,有問題!
我这里就是不行啊 select * from( select top 50 * from( select top 100 * from eis_user.f_bill order by f_bill_no asc ,entry_no asc ) as b order by f_bill_no desc,entry_no desc ) as c order by f_bill_no asc ,entry_no asc
好了,我知道了在2000里你這樣做沒問題 7.0不可以,最後一次排序他憂化了 select * from eve_log where eve_log_id in (select top 30 eve_log_id from (select top 100 * from eve_log as c order by eve_log_id) as a order by eve_log_id desc) order by eve_log_id asc
我在7.0在測過了,你不要告我你是6.5所以不行哦,那我可要faint了
多谢!我是在7.0上做的,后来到ACCESS2000下也没有问题,不知道在ORACLE下会怎么样
最后的排序真的不行? 身边没安装SQL的机器, 有时间我也试试. 真没想到会有这样的事!呵呵
在SQL2000中,我前面给的语句是正确的。 还没在SQL7中测试。
select * from MyTabl where rowunm between 50 and 100 ORDER BY field1
楼上的,rowunm是什么东西,语句不能通过
你是用ORACLE??我要求是ACCESS、SQL SERVER 、ORACLE全部都可以用
我也想请问狗狗,哪个字段rowunm是什么啊? 在MS SQL 7.0或者2000里头能不能用?
我只知道oracle可以用这个字段表示你选出的记录的顺序排列.这是每个表中一伪列
上面看到有人误用oracle的rownum为 select * from MyTabl where rowunm between 50 and 100 ORDER BY field1 这是不对的,结果集一定为空。 为什么?因为oracle将第一行与之判断后发现不符合就不再做下去。在处理rownum时是与处理一般条件不同的。 我可以这样写: select * from( select * from ( (select * from (select * from MyTabl where rownum<=100 order by field1) order by field1 desc) ) where rownum<=51 ) order by field1哈哈,是不是比较复杂。先取100条,倒一下,取前面51条,最后再倒一下。四步搞定。 不过在实际开发中根本用不着如此麻烦。比如说如果使用sql server,那么可以充分发挥临时表优势,先将数据select into到临时表,爱怎么做就怎么做,实在不行可以添加一个辅助列。
不好意思,上面的语句写错了括号,应该是 select * from( select * from( select * from( select * from MyTabl where rownum<=100 order by field1 ) order by field1 desc) ) where rownum<=51 ) order by field1;大家可以试一下。这是ORacle的代码。
如果实际只有80条记录,上面的方法显然是不对的 select * from( select * from ( (select * from (select * from MyTabl where rownum<=100 order by field1) order by field1 desc) ) where rownum<=(select max(rownum)-50 from MyTabl) ) order by field1;
(select top 50 * from (select top 100 * from MyTabl ORDER BY field1) as a ORDER BY field1 desc) as b
ORDER BY field1
(select top 100 * from MyTabl ORDER BY field1) ss
order by field1 desc;
select * from
(select top 30 * from
(select top 100 * from eve_log as c order by eve_log_id)
as a order by eve_log_id desc)
as b order by eve_log_id asc没错吧,就是倒不过来
的排序没加asc或desc,就是采用默认设置
select * from(
select top 50 * from(
select top 100 * from eis_user.f_bill order by f_bill_no asc ,entry_no asc
)
as b order by f_bill_no desc,entry_no desc
)
as c order by f_bill_no asc ,entry_no asc
TMD 7.0 和 2000 策略都不一樣,7.0只有一次sort,2000有兩次
7.0不可以,最後一次排序他憂化了
select * from eve_log where eve_log_id in
(select top 30 eve_log_id from
(select top 100 * from eve_log as c order by eve_log_id)
as a order by eve_log_id desc)
order by eve_log_id asc
还没在SQL7中测试。
在MS SQL 7.0或者2000里头能不能用?
select * from MyTabl where rowunm between 50 and 100 ORDER BY field1
这是不对的,结果集一定为空。
为什么?因为oracle将第一行与之判断后发现不符合就不再做下去。在处理rownum时是与处理一般条件不同的。
我可以这样写:
select * from(
select * from
(
(select * from
(select * from MyTabl where rownum<=100 order by field1)
order by field1 desc)
)
where rownum<=51
)
order by field1哈哈,是不是比较复杂。先取100条,倒一下,取前面51条,最后再倒一下。四步搞定。
不过在实际开发中根本用不着如此麻烦。比如说如果使用sql server,那么可以充分发挥临时表优势,先将数据select into到临时表,爱怎么做就怎么做,实在不行可以添加一个辅助列。
select * from(
select * from(
select * from(
select * from MyTabl where rownum<=100 order by field1
) order by field1 desc)
)
where rownum<=51
)
order by field1;大家可以试一下。这是ORacle的代码。
select * from(
select * from
(
(select * from
(select * from MyTabl where rownum<=100 order by field1)
order by field1 desc)
)
where rownum<=(select max(rownum)-50 from MyTabl)
)
order by field1;