sql - 当我只有一周中的某一天时，获取天数

Question

我对 MT0使用间隔和日期对这个问题的回答很感兴趣。我一直在努力寻找一种不同的方式来回答这个问题，我开始想知道一些事情。

仅使用 MT0 设置的间隔：

with weekly_shifts(shift_date,start_time,end_time) as
(SELECT 'MON', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL UNION ALL
 SELECT 'TUE', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL UNION ALL
 SELECT 'WED', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL UNION ALL
 SELECT 'THU', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL UNION ALL
 SELECT 'FRI', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL)

如果我只有DY格式 ( MON,TUE,WED) 中的星期几，并且我想获得日期 ( 2,3,4) 的数字版本，那么最简单的方法是什么？

我能想到的唯一想法是这样的：

select to_char(next_day(sysdate,shift_date),'D') SHIFT_NUM,
       weekly_shifts.*
from weekly_shifts

标签： sqloracle

Answer 1

一种可能性是使用 row_number() 和 rownum。需要彻底测试，因为您的示例可能已简化：

with weekly_shifts
AS
(
SELECT 'MON' shift_day, INTERVAL '09:00' HOUR TO MINUTE start_time, INTERVAL '18:00' HOUR TO MINUTE end_time FROM DUAL 
UNION ALL
SELECT 'TUE', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL 
UNION ALL
SELECT 'WED', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL 
UNION ALL
SELECT 'THU', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL 
UNION ALL
SELECT 'FRI', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL
UNION ALL
SELECT 'MON' shift_day, INTERVAL '09:00' HOUR TO MINUTE start_time, INTERVAL '18:00' HOUR TO MINUTE end_time FROM DUAL 
UNION ALL
SELECT 'TUE', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL 
UNION ALL
SELECT 'WED', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL 
UNION ALL
SELECT 'THU', INTERVAL '10:00' HOUR TO MINUTE, INTERVAL '19:00' HOUR TO MINUTE FROM DUAL 
UNION ALL
SELECT 'FRI', INTERVAL '09:00' HOUR TO MINUTE, INTERVAL '18:00' HOUR TO MINUTE FROM DUAL
)
SELECT ROW_NUMBER() OVER (PARTITION BY reset_week ORDER BY reset_week)+1 day_number, shift_day, start_time, end_time, reset_week
  FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY shift_day ORDER BY rownum) reset_week, shift_day, start_time, end_time FROM weekly_shifts
ORDER BY rownum
)
ORDER BY reset_week
/

输出：

day_number  shift_day   start_time              end_time                reset_week
2           MON         +00 09:00:00.000000     +00 18:00:00.000000     1
3           TUE         +00 10:00:00.000000     +00 19:00:00.000000     1
4           WED         +00 09:00:00.000000     +00 18:00:00.000000     1
5           THU         +00 10:00:00.000000     +00 19:00:00.000000     1
6           FRI         +00 09:00:00.000000     +00 18:00:00.000000     1
2           MON         +00 09:00:00.000000     +00 18:00:00.000000     2
3           TUE         +00 10:00:00.000000     +00 19:00:00.000000     2
4           WED         +00 09:00:00.000000     +00 18:00:00.000000     2
5           THU         +00 10:00:00.000000     +00 19:00:00.000000     2
6           FRI         +00 09:00:00.000000     +00 18:00:00.000000     2