Oracle SQL实现按当前日期筛选对应星期字段非空用户

Got it, let's work through this problem. You're already halfway there with the to_char(sysdate,'D') snippet—we just need to map that numeric value to your day-specific columns and add a filter to grab users where the current day's column isn't null.

First, Verify the Weekday Number Mapping #

Oracle's to_char(sysdate,'D') returns a string representing the day of the week, but the numbering can vary based on your NLS_TERRITORY settings. To confirm what each number corresponds to, run this quick check first:

SELECT TO_CHAR(SYSDATE, 'D') AS day_number, TO_CHAR(SYSDATE, 'DAY') AS day_name
FROM DUAL;

By default, most setups use:

• '1' = Sunday
• '2' = Monday
• '3' = Tuesday
• '4' = Wednesday
• '5' = Thursday
• '6' = Friday
• '7' = Saturday

Adjust the mappings below if your output shows a different numbering.

Option 1: Readable CASE Statement #

This approach is straightforward and easy to modify if your weekday numbering changes. Replace your_table_name with the actual name of your table:

SELECT "USER"
FROM your_table_name
WHERE CASE TO_CHAR(SYSDATE, 'D')
          WHEN '1' THEN SUNDAY
          WHEN '2' THEN MONDAY
          WHEN '3' THEN TUESDAY
          WHEN '4' THEN WEDNESDAY
          WHEN '5' THEN THURSDAY
          WHEN '6' THEN FRIDAY
          WHEN '7' THEN SATURDAY
      END IS NOT NULL;

How this works:

• The CASE statement takes the current day's number and maps it to the corresponding column in your table.
• We then filter rows where that mapped column is not null—exactly what you need to get the right user for the day.

Option 2: Concise DECODE Function #

If you prefer a more compact query, use Oracle's DECODE function instead of CASE:

SELECT "USER"
FROM your_table_name
WHERE DECODE(TO_CHAR(SYSDATE, 'D'),
             '1', SUNDAY,
             '2', MONDAY,
             '3', TUESDAY,
             '4', WEDNESDAY,
             '5', THURSDAY,
             '6', FRIDAY,
             '7', SATURDAY) IS NOT NULL;

This does the exact same thing as the CASE statement, just in a shorter format.

Example Behavior #

• On Monday (when TO_CHAR(SYSDATE,'D') returns '2'), the query checks the MONDAY column. It will return 123456 since that user's MONDAY value is 18 (not null).
• On Tuesday (returning '3'), it checks the TUESDAY column and returns 789023 because their TUESDAY value is 3 (not null).

Notes #

• "USER" is a reserved keyword in Oracle, so we wrap it in double quotes to reference the column correctly.
• If your NLS_TERRITORY uses a different numbering (e.g., '1' = Monday), just reorder the WHEN clauses in CASE or the arguments in DECODE to match your verified mapping.

内容的提问来源于stack exchange，提问作者XD4