今天碰到一个很奇怪的问题,Oracle中Order By与序列同时用报错,情况是这样的。
1、这样直接一起用报错:
SELECT W_T_SEQ.Nextval,
IP.ROW_NO,
IP.NUMBER
FROM IMP_T IP
WHERE NUMBER = '80112'
ORDER BY IP.ROW_NO
当时不明白为什么,后来查看了下资料,修改了下代码,结果通过了,感觉很有趣的一个问题,修改后的代码如下:
2、使用嵌套查询:
SELECT W_T_SEQ.NEXTVAL,
NUMBER,
ROW_NO
FROM (SELECT NUMBER,
ROW_NO
FROM IMP_T IP
WHERE IP.NUMBER = '80112'
ORDER BY IP.ROW_NO)
3、group by,order by 和rownum并用实例:
select name
from (
select name,min(row_no)
from table_name
where id = '123456'
group by name
having count(1) > 1
roder by row_no)
where rownum = 1
呵呵,总结下,以免忘记,如果有童鞋碰到一样的问题时,也可以参考!
1、这样直接一起用报错:
SELECT W_T_SEQ.Nextval,
IP.ROW_NO,
IP.NUMBER
FROM IMP_T IP
WHERE NUMBER = '80112'
ORDER BY IP.ROW_NO
当时不明白为什么,后来查看了下资料,修改了下代码,结果通过了,感觉很有趣的一个问题,修改后的代码如下:
2、使用嵌套查询:
SELECT W_T_SEQ.NEXTVAL,
NUMBER,
ROW_NO
FROM (SELECT NUMBER,
ROW_NO
FROM IMP_T IP
WHERE IP.NUMBER = '80112'
ORDER BY IP.ROW_NO)
3、group by,order by 和rownum并用实例:
select name
from (
select name,min(row_no)
from table_name
where id = '123456'
group by name
having count(1) > 1
roder by row_no)
where rownum = 1
呵呵,总结下,以免忘记,如果有童鞋碰到一样的问题时,也可以参考!