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条数据,谢谢了!!!
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 就应该好了吧
做一个子查询就可以了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条:
再问一下,这个效率是做好的吗?也就是这条sql语句可以优化不???
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
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;
(
case FEE_RIDE_STATE
when '待预订' then '待预订'
else '已订完'
end
) FEE_RIDE_STATE from VW_FREE_RIDE order by cre_date desc
)where rownum<7 就应该好了吧
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;
(
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条:
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
(
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;
比
where rownum <7;
效率搞。
高吧~