Oracle SQL查询：列出2018.1.3-2018.1.31间周日起始的周起止日期

Got it, here's a tailored solution to generate the weekly start and end dates you need, aligned with your specified date range (03/01/2018 to 31/01/2018) and week structure (starts on Sunday, ends on Thursday):

WITH date_range AS (
    -- Generate every date in the target range
    SELECT TRUNC(TO_DATE('03/01/2018', 'DD/MM/YYYY')) + LEVEL - 1 AS calendar_date
    FROM dual
    CONNECT BY TRUNC(TO_DATE('03/01/2018', 'DD/MM/YYYY')) + LEVEL - 1 <= TRUNC(TO_DATE('31/01/2018', 'DD/MM/YYYY'))
),
weekly_groups AS (
    SELECT 
        calendar_date,
        -- Calculate the Sunday start of the week for each date
        TRUNC(calendar_date) - (TO_CHAR(calendar_date, 'D') - 1) AS week_start,
        -- Calculate Thursday end of the week, or cap at the end date if it's a partial week
        LEAST(TRUNC(calendar_date) - (TO_CHAR(calendar_date, 'D') - 1) + 4, TO_DATE('31/01/2018', 'DD/MM/YYYY')) AS week_end
    FROM date_range
)
-- Select unique week pairs and format the output
SELECT DISTINCT
    TO_CHAR(week_start, 'DD/MM/YYYY') AS StartWeek,
    TO_CHAR(week_end, 'DD/MM/YYYY') AS EndWeek
FROM weekly_groups
ORDER BY week_start;

Breakdown of how this works: #

1. date_range CTE: Uses CONNECT BY to generate a row for every date between your start and end dates—this gives us a base list of days to group into weeks.
2. weekly_groups CTE:
   • week_start: Takes each date and subtracts the number of days since the last Sunday (using TO_CHAR(calendar_date, 'D') which returns 1 for Sunday in standard NLS settings).
   • week_end: Adds 4 days to the week start (Sunday + 4 days = Thursday) and uses LEAST() to make sure we don't go beyond your specified end date for the final partial week.
3. Final SELECT: Uses DISTINCT to get only one entry per week, formats dates to match your required output, and sorts them chronologically.

Sample Output (matches your example): #

StartWeek EndWeek
03/01/2018 04/01/2018
07/01/2018 11/01/2018
14/01/2018 18/01/2018
21/01/2018 25/01/2018
28/01/2018 31/01/2018

Note: If your Oracle NLS settings use a different day numbering (e.g., Monday = day 1), adjust the (TO_CHAR(calendar_date, 'D') - 1) part to align Sunday as the week start. For example, if Sunday is day 7, you'd use (TO_CHAR(calendar_date, 'D') - 7) instead.

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