Oracle sql 按当前日期对工作日进行排序 [英] Oracle sql sort week days by current day
本文介绍了Oracle sql 按当前日期对工作日进行排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试根据以下顺序对日期进行排序:星期六、星期日、星期一、星期二、星期三、星期四、星期五.我正在尝试使用案例:
I am trying to sort the days based on the order: Saturday, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday. I am trying using case:
select day,
CASE day
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 5 THEN 5
WHEN 6 THEN 6
WHEN 7 THEN 7
else 0
END as day_nr
from week where day in (1,2,3,4,5,6,7)
order by day_nr asc
当我选择一周中的所有日子时,这没问题.但是,如果我只想要 1、5、6 天,则排序不正确.获取第一天 - 星期一.如何进行?
This is ok when I select all the days of the week. But if I want only for the day 1,5,6 the ordering is not correct. Gets the first day -Monday. How to proceed?
推荐答案
在 Oracle 中,第 1 天默认为星期日.
In Oracle day 1 is Sunday by default.
SELECT * FROM
(
SELECT trunc(sysdate) + LEVEL-1 my_dt
, TO_CHAR(trunc(sysdate) + LEVEL-1, 'DY') Wk_Day
, TO_CHAR(trunc(sysdate) + LEVEL-2, 'D' ) Day#
FROM dual
CONNECT BY LEVEL <= 10
)
WHERE Day# IN (1,5,6)
ORDER BY my_dt, Day#
/
MY_DT WK_DAY DAY#
------------------------
5/10/2013 FRI 5
5/11/2013 SAT 6
5/13/2013 MON 1
5/17/2013 FRI 5
5/18/2013 SAT 6
这篇关于Oracle sql 按当前日期对工作日进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文