SELECT * FROM
(
SELECT RUN_.*,ROWNUM
FROM
(
SELECT MAX(FLIGHT_ORDER_ID) FDI,MAX(ORDER_TIME) OT,
MAX((CASE WHEN FLIGHT_ORDER_STATE=0 THEN '无效' when FLIGHT_ORDER_STATE=1 then
'已出票' WHEN FLIGHT_ORDER_STATE=2 THEN'新订单' WHEN FLIGHT_ORDER_STATE=3 THEN
'已支付' WHEN FLIGHT_ORDER_STATE=4 THEN '团队申请' WHEN FLIGHT_ORDER_STATE=5 THEN
'已申请团队' end)) FOS,
MAX(FLIGHT_ORDER_STATE) FOS,MAX(AIR_CO_CHS) ACC,
MAX(SEAT) seat,MAX(AIR_NO) AN,MAX(FROM_CITY_CHS) FCC,
MAX(TO_CITY_CHS) TCC,MAX(PNR) pnr,MAX(FROM_TIME) FT,
MAX(TO_TIME) TT,MAX(LINK_MAN) LM,MAX(LINK_MOBILE) LB,
MAX(LINK_POSTCADE) LP,MAX(LINK_ADDRESS) LA,MAX(PASSENGER_NAME) PN,
MAX(PASSENGER_CODE) PC,MAX(TRICK_NO) TN,
MAX((CASE WHEN PASSENGER_STATE=0 THEN '无效' WHEN PASSENGER_STATE=1 THEN '有效' WHEN PASSENGER_STATE=10 THEN '废票失败'
WHEN PASSENGER_STATE=11 THEN '已废票' WHEN PASSENGER_STATE=12 THEN '废票中(供应商未处理)' WHEN PASSENGER_STATE=13 THEN '废票中(供应商已处理)' WHEN PASSENGER_STATE=20 THEN '退票失败'
WHEN PASSENGER_STATE=21 THEN '已退票' WHEN PASSENGER_STATE=22 THEN '退票中(供应商未处理)' WHEN PASSENGER_STATE=23 THEN '退票中(供应商已处理)' WHEN PASSENGER_STATE=32 THEN '改签中' end)) PS,
MAX(SOLO_ORDER_AMT) SOA,MAX(SOLO_PAY_AMT) SPA
FROM FLIGHT_ORDERS FO INNER JOIN FLIGHTS FS USING(FLIGHT_ORDER_ID) INNER JOIN VIPS V USING(VIP_ID) INNER JOIN ORDER_PASSENGERS OP USING(FLIGHT_ORDER_ID)
GROUP BY FLIGHT_ORDER_ID ORDER BY FLIGHT_ORDER_ID DESC
)
RUN_ WHERE ROWNUM<=10 ) WHERE ROWNUM>=0
很奇怪,最里面的SELECT 正常 到了第二个SELECT RUN_.*,ROWNUM就查不出数据了 请大侠帮忙啊!
(
SELECT RUN_.*,ROWNUM
FROM
(
SELECT MAX(FLIGHT_ORDER_ID) FDI,MAX(ORDER_TIME) OT,
MAX((CASE WHEN FLIGHT_ORDER_STATE=0 THEN '无效' when FLIGHT_ORDER_STATE=1 then
'已出票' WHEN FLIGHT_ORDER_STATE=2 THEN'新订单' WHEN FLIGHT_ORDER_STATE=3 THEN
'已支付' WHEN FLIGHT_ORDER_STATE=4 THEN '团队申请' WHEN FLIGHT_ORDER_STATE=5 THEN
'已申请团队' end)) FOS,
MAX(FLIGHT_ORDER_STATE) FOS,MAX(AIR_CO_CHS) ACC,
MAX(SEAT) seat,MAX(AIR_NO) AN,MAX(FROM_CITY_CHS) FCC,
MAX(TO_CITY_CHS) TCC,MAX(PNR) pnr,MAX(FROM_TIME) FT,
MAX(TO_TIME) TT,MAX(LINK_MAN) LM,MAX(LINK_MOBILE) LB,
MAX(LINK_POSTCADE) LP,MAX(LINK_ADDRESS) LA,MAX(PASSENGER_NAME) PN,
MAX(PASSENGER_CODE) PC,MAX(TRICK_NO) TN,
MAX((CASE WHEN PASSENGER_STATE=0 THEN '无效' WHEN PASSENGER_STATE=1 THEN '有效' WHEN PASSENGER_STATE=10 THEN '废票失败'
WHEN PASSENGER_STATE=11 THEN '已废票' WHEN PASSENGER_STATE=12 THEN '废票中(供应商未处理)' WHEN PASSENGER_STATE=13 THEN '废票中(供应商已处理)' WHEN PASSENGER_STATE=20 THEN '退票失败'
WHEN PASSENGER_STATE=21 THEN '已退票' WHEN PASSENGER_STATE=22 THEN '退票中(供应商未处理)' WHEN PASSENGER_STATE=23 THEN '退票中(供应商已处理)' WHEN PASSENGER_STATE=32 THEN '改签中' end)) PS,
MAX(SOLO_ORDER_AMT) SOA,MAX(SOLO_PAY_AMT) SPA
FROM FLIGHT_ORDERS FO INNER JOIN FLIGHTS FS USING(FLIGHT_ORDER_ID) INNER JOIN VIPS V USING(VIP_ID) INNER JOIN ORDER_PASSENGERS OP USING(FLIGHT_ORDER_ID)
GROUP BY FLIGHT_ORDER_ID ORDER BY FLIGHT_ORDER_ID DESC
)
RUN_ WHERE ROWNUM<=10 ) WHERE ROWNUM>=0
很奇怪,最里面的SELECT 正常 到了第二个SELECT RUN_.*,ROWNUM就查不出数据了 请大侠帮忙啊!
解决方案 »
- Data masking 求助。
- 查看和修改数据库名(oracle,sql server,mysql)
- linux新建一个用户登陆oracle报错
- 求 一个按照从大范围到小范围,级别筛选纪录的sql写法,急~~在线等!!!!
- 急——数据库触发器创建后无法使用
- 求助:杀毒时把htpasswd.exe和startapache.exe文件给误删除,请问如何恢复?
- 备份问题
- 高手共同解决大问题 在线等待。
- [急急急!!!]请教一个OCI函数的问题
- Pb如何正确显示oracle中数据字符集为iso8859-1的中文数据。多谢各位!
- 如何让oracle在事物提交的时候才验证外键约束?
- oracle 透明网关问题
(
SELECT RUN_.*,ROWNUM rn
FROM
(
SELECT MAX(FLIGHT_ORDER_ID) FDI,MAX(ORDER_TIME) OT,
MAX((CASE WHEN FLIGHT_ORDER_STATE=0 THEN '无效' when FLIGHT_ORDER_STATE=1 then
'已出票' WHEN FLIGHT_ORDER_STATE=2 THEN'新订单' WHEN FLIGHT_ORDER_STATE=3 THEN
'已支付' WHEN FLIGHT_ORDER_STATE=4 THEN '团队申请' WHEN FLIGHT_ORDER_STATE=5 THEN
'已申请团队' end)) FOS,
MAX(FLIGHT_ORDER_STATE) FOS,MAX(AIR_CO_CHS) ACC,
MAX(SEAT) seat,MAX(AIR_NO) AN,MAX(FROM_CITY_CHS) FCC,
MAX(TO_CITY_CHS) TCC,MAX(PNR) pnr,MAX(FROM_TIME) FT,
MAX(TO_TIME) TT,MAX(LINK_MAN) LM,MAX(LINK_MOBILE) LB,
MAX(LINK_POSTCADE) LP,MAX(LINK_ADDRESS) LA,MAX(PASSENGER_NAME) PN,
MAX(PASSENGER_CODE) PC,MAX(TRICK_NO) TN,
MAX((CASE WHEN PASSENGER_STATE=0 THEN '无效' WHEN PASSENGER_STATE=1 THEN '有效' WHEN PASSENGER_STATE=10 THEN '废票失败'
WHEN PASSENGER_STATE=11 THEN '已废票' WHEN PASSENGER_STATE=12 THEN '废票中(供应商未处理)' WHEN PASSENGER_STATE=13 THEN '废票中(供应商已处理)' WHEN PASSENGER_STATE=20 THEN '退票失败'
WHEN PASSENGER_STATE=21 THEN '已退票' WHEN PASSENGER_STATE=22 THEN '退票中(供应商未处理)' WHEN PASSENGER_STATE=23 THEN '退票中(供应商已处理)' WHEN PASSENGER_STATE=32 THEN '改签中' end)) PS,
MAX(SOLO_ORDER_AMT) SOA,MAX(SOLO_PAY_AMT) SPA
FROM FLIGHT_ORDERS FO INNER JOIN FLIGHTS FS USING(FLIGHT_ORDER_ID) INNER JOIN VIPS V USING(VIP_ID) INNER JOIN ORDER_PASSENGERS OP USING(FLIGHT_ORDER_ID)
GROUP BY FLIGHT_ORDER_ID ORDER BY FLIGHT_ORDER_ID DESC
)
RUN_ WHERE ROWNUM <=10 ) WHERE rn>=0
union select 2 from dual
union select 3 from dual
union select 4 from dual
union select 5 from dual
union select 6 from dual)SELECT * FROM
(
SELECT RUN_.*,ROWNUM rn
FROM
(
select * from a order by 1 desc
)
RUN_ WHERE ROWNUM <=10 ) WHERE rn>=0 --里面有数据就可以啊