请帮忙修改一条sql语句？？？

[qingYun1029]

select fr_id,take_car_city,back_car_city,car_model,NORMAL_PRICE,
(
    case FEE_RIDE_STATE
    when '待预订' then '待预订'
    else '已订完'
    end
) FEE_RIDE_STATE from VW_FREE_RIDE where rownum<7 order by cre_date desc

修改要求，先按时间倒叙，然后取前6条数据，谢谢了！！！

[zdj624489750]

select * from (select fr_id,take_car_city,back_car_city,car_model,NORMAL_PRICE,
(
case FEE_RIDE_STATE
when '待预订' then '待预订'
else '已订完'
end
) FEE_RIDE_STATE from VW_FREE_RIDE order by cre_date desc
)where rownum<7 就应该好了吧

[opps_zhou]

做一个子查询就可以了

select *
  from (select fr_id,take_car_city,back_car_city,car_model,NORMAL_PRICE,
            (
                case FEE_RIDE_STATE
                when '待预订' then '待预订'
                else '已订完'
                end
            ) FEE_RIDE_STATE,
            row_number() over(order by cre_date desc) as isort from VW_FREE_RIDE
        ) t
 where isort <= 6;


select *
  from (select fr_id,take_car_city,back_car_city,car_model,NORMAL_PRICE,
            (
                case FEE_RIDE_STATE
                when '待预订' then '待预订'
                else '已订完'
                end
            ) FEE_RIDE_STAT from VW_FREE_RIDE order by cre_date desc
        ) t
 where rownum <= 6;

[我心飞翔]

实测：

CREATE TABLE T66
(
    MyTime DATE,    
    Money  NUMBER(4)
);
INSERT INTO T66 VALUES(to_date('2011-12-01', 'YYYY-MM-DD'), 1);
INSERT INTO T66 VALUES(to_date('2011-12-02', 'YYYY-MM-DD'), 2);
INSERT INTO T66 VALUES(to_date('2011-12-03', 'YYYY-MM-DD'), 3);
INSERT INTO T66 VALUES(to_date('2011-12-04', 'YYYY-MM-DD'), 4);
INSERT INTO T66 VALUES(to_date('2011-12-05', 'YYYY-MM-DD'), 5);

按时间倒序取3条：

[qingYun1029]

谢谢各位了。。。

[qingYun1029]

再问一下，这个效率是做好的吗？也就是这条sql语句可以优化不？？？

[lzz7658823]

select tt.*
from (select fr_id,
take_car_city,
back_car_city,
car_model,
NORMAL_PRICE,
(case
when FEE_RIDE_STATE = '待预订' then
'待预订'
else
'已订完'
end) FEE_RIDE_STATE
from VW_FREE_RIDE
ORDER BY DATETIME desc --假设你表里的时间字段为DATETIME
) tt
where rownum < 7

[Larry]

WITH WTTB AS 
(
  SELECT FR_ID,TAKE_CAR_CITY,BACK_CAR_CITY,CAR_MODEL,NORMAL_PRICE,CRE_DATE,
  CASE FEE_RIDE_STATE WHEN '待预订' THEN '待预订' ELSE '已订完' END FEE_RIDE_STATE 
  FROM VW_FREE_RIDE ORDER BY CRE_DATE DESC 
) 
SELECT FR_ID,TAKE_CAR_CITY,BACK_CAR_CITY,CAR_MODEL,NORMAL_PRICE,FEE_RIDE_STATE 
FROM WTTB WHERE ROWNUM < 7;

[hllfl]

where rownum <= 6;
比
where rownum <7;
效率搞。

[pl_mm5]

where rownum <7;
高吧~