sql - 使用Oracle SQL，如何输出星期几和星期几？

Question

使用 Oracle SQL，您如何为以下对象创建结果集:

星期几 (1-7)

日期名称(星期一、星期二、星期三等)

例如:

日期名称
1 星期一
2 星期二
3 星期三
4 星期四
5 星期五
6 星期六
7 星期日

Answer 1

弗洛林的回答是我会怎么做，但你需要小心 NLS设置。星期几受 NLS 领土的影响，所以如果我像在美国一样运行它，它会起作用:

alter session set nls_territory = 'AMERICA';

select to_char(sysdate, 'D') as d, to_char(sysdate, 'Day') as day from dual;

D DAY
- ------------------------------------
6 Friday

select level as dow,
    to_char(trunc(sysdate ,'D') + level, 'Day') as day
from dual
connect by level <= 7;

DOW DAY
--- ------------------------------------
  1 Monday
  2 Tuesday
  3 Wednesday
  4 Thursday
  5 Friday
  6 Saturday
  7 Sunday

但是在英国运行的相同查询是休息日:

alter session set nls_territory = 'UNITED KINGDOM';

select to_char(sysdate, 'D') as d, to_char(sysdate, 'Day') as day from dual;

D DAY
- ------------------------------------
5 Friday

select level as dow,
    to_char(trunc(sysdate ,'D') + level, 'Day') as day
from dual
connect by level <= 7;

DOW DAY
--- ------------------------------------
  1 Tuesday
  2 Wednesday
  3 Thursday
  4 Friday
  5 Saturday
  6 Sunday
  7 Monday

...我需要调整计算以纠正:

select level as dow,
    to_char(trunc(sysdate ,'D') + level - 1, 'Day') as day
from dual
connect by level <= 7;

DOW DAY
--- ------------------------------------
  1 Monday
  2 Tuesday
  3 Wednesday
  4 Thursday
  5 Friday
  6 Saturday
  7 Sunday

如果需要，您还可以单独指定用于日期名称的语言:

select level as dow,
    to_char(trunc(sysdate ,'day') + level - 1, 'Day',
        'NLS_DATE_LANGUAGE=FRENCH') as day
from dual
connect by level <= 7;

DOW DAY
--- --------------------------------
  1 Lundi
  2 Mardi
  3 Mercredi
  4 Jeudi
  5 Vendredi
  6 Samedi
  7 Dimanche

to_char() with nls_date_language 的文档和 day of the week ，以及更多在 the globalisation support guide .