你把order by 放在那个子查询里面...是想证明什么,你叫oracle查询ID的时候按你排序顺序查找吗? 如果你的子查询出来是1,10,4,7,2,9跟1,2,4,7,9,10.对你的查找有什么区别? 如果没有top n的话,子查询加上order by完全没有意义..所以oracle也把这个认为是错误语法
你好,我就说要运用top 的语法,原句其实是这样子的 "select arp.* from db32annualrepairplan arp where arp.db32_1 in (select top 1 arp1.db32_1 from db32annualrepairplan arp1 where arp1.db32_8 = '3' and arp1.db32_5 = arp.db32_5 and arp1.db32_3 = arp.db32_3 order by arp1.db32_2 desc) order by arp.db32_1 DESC" 开始是显示top 1这里的错误,我知道在oracle中应该用where rownum=1来代替,可是之后又出现括号里order by arp1.db32_2 desc这个缺少右括号的错误,删了就可以了,我想是让括号内先排序然后选择第一个的记录,然后再进行括号外的语句。 谢谢您了,帮我解答一下。
如果你非要用这样的 你外面就得还套一层,上面不是说了from字句可以用,where 字句不可以用的 select arp.* from db32annualrepairplan arp where arp.db32_1 in( select * from(select top 1 arp1.db32_1 from db32annualrepairplan arp1 where arp1.db32_8 = '3' and arp1.db32_5 = arp.db32_5 and arp1.db32_3 = arp.db32_3 order by arp1.db32_2 desc)) order by arp.db32_1 DESC"
oracle考虑到你的rownum等于或IN某个列表时会出现某个值就没有必要排序了 如果你非要rownum与order by一起排序的话 select arp.* from db32annualrepairplan arp where arp.db32_1 in (select arp1.db32_1 from db32annualrepairplan arp1 where arp1.db32_8 = '3' and arp1.db32_5 = arp.db32_5 and arp1.db32_3 = arp.db32_3 AND rownum<2 order by arp1.db32_2 desc) order by arp.db32_1 DESC你也可以对select arp1.db32_1 from db32annualrepairplan arp1 where arp1.db32_8 = '3' and arp1.db32_5 = arp.db32_5 and arp1.db32_3 = arp.db32_3排序完然后再rownum小于某几行...然后再排序. 其实如果是单条的话,用MAX或MIN不是就搞定啦
select arp.* from db32annualrepairplan arp where arp.db32_1 in( select * from(select top 1 arp1.db32_1 from db32annualrepairplan arp1 where arp1.db32_8 = '3' and arp1.db32_5 = arp.db32_5 and arp1.db32_3 = arp.db32_3 order by arp1.db32_2 desc)) order by arp.db32_1 DESC" 这里又显示arp.db32_3这个无效标示符,嵌套的太多了吗?都读不到arp最外面一层的字段
你既然是一条,你直接max()函数不行么,select arp.* from db32annualrepairplan arp where arp.db32_1 in( select max(arp1.db32_1) from db32annualrepairplan arp1 where arp1.db32_8 = '3' and arp1.db32_5 = arp.db32_5 and arp1.db32_3 = arp.db32_3 ) order by arp.db32_1 DESC
2、order by执行顺序是 在数据查询出以后的再进行的排序。
如果你的子查询出来是1,10,4,7,2,9跟1,2,4,7,9,10.对你的查找有什么区别?
如果没有top n的话,子查询加上order by完全没有意义..所以oracle也把这个认为是错误语法
"select arp.* from db32annualrepairplan arp where arp.db32_1 in
(select top 1 arp1.db32_1 from db32annualrepairplan arp1 where arp1.db32_8 = '3' and arp1.db32_5 = arp.db32_5 and arp1.db32_3 = arp.db32_3 order by arp1.db32_2 desc) order by arp.db32_1 DESC"
开始是显示top 1这里的错误,我知道在oracle中应该用where rownum=1来代替,可是之后又出现括号里order by arp1.db32_2 desc这个缺少右括号的错误,删了就可以了,我想是让括号内先排序然后选择第一个的记录,然后再进行括号外的语句。
谢谢您了,帮我解答一下。
select arp.* from db32annualrepairplan arp where arp.db32_1 in(
select * from(select top 1 arp1.db32_1 from db32annualrepairplan arp1 where arp1.db32_8 = '3' and arp1.db32_5 = arp.db32_5 and arp1.db32_3 = arp.db32_3 order by arp1.db32_2 desc)) order by arp.db32_1 DESC"
如果你非要rownum与order by一起排序的话
select arp.* from db32annualrepairplan arp where arp.db32_1 in
(select arp1.db32_1 from db32annualrepairplan arp1 where arp1.db32_8 = '3' and arp1.db32_5 = arp.db32_5 and arp1.db32_3 = arp.db32_3 AND rownum<2 order by arp1.db32_2 desc) order by arp.db32_1 DESC你也可以对select arp1.db32_1 from db32annualrepairplan arp1 where arp1.db32_8 = '3' and arp1.db32_5 = arp.db32_5 and arp1.db32_3 = arp.db32_3排序完然后再rownum小于某几行...然后再排序.
其实如果是单条的话,用MAX或MIN不是就搞定啦
select * from(select top 1 arp1.db32_1 from db32annualrepairplan arp1 where arp1.db32_8 = '3' and arp1.db32_5 = arp.db32_5 and arp1.db32_3 = arp.db32_3 order by arp1.db32_2 desc)) order by arp.db32_1 DESC"
这里又显示arp.db32_3这个无效标示符,嵌套的太多了吗?都读不到arp最外面一层的字段
select max(arp1.db32_1) from db32annualrepairplan arp1 where arp1.db32_8 = '3' and arp1.db32_5 = arp.db32_5 and arp1.db32_3 = arp.db32_3 ) order by arp.db32_1 DESC