嘿，我来帮你搞定这个需求！首先咱们得明确几个核心要求：只取已发货（SHIPMENT='Y'）的记录，在给定的起止日期范围内按周分组，最后输出Weeks（格式如week1/WEEK3）和对应的商品名称。

下面分几种场景给你具体的实现方案：

方案1：按起始日期开始的连续7天为一周计算 #

这种方式把你传入的起始日期作为第1周的起点，每7天划分为一个周，适合需要自定义周起始的场景：

WITH date_range AS (
    -- 这里替换成你的起止日期，或者用绑定变量:p_start_date、:p_end_date
    SELECT TO_DATE('19/02/2021', 'DD/MM/YYYY') AS start_dt,
           TO_DATE('26/03/2021', 'DD/MM/YYYY') AS end_dt
    FROM DUAL
),
weekly_products AS (
    SELECT 
        PRODUCTS,
        TR_DATE,
        -- 计算当前日期属于起始日期后的第几个周
        'week' || CEIL((TR_DATE - dr.start_dt)/7 + 1) AS week_label
    FROM DR_TRANSPORT dt
    CROSS JOIN date_range dr
    WHERE dt.SHIPMENT = 'Y'
      AND dt.TR_DATE BETWEEN dr.start_dt AND dr.end_dt
)
SELECT 
    -- 适配示例里的大小写（比如WEEK3大写），可以按需调整
    CASE WHEN week_label = 'week3' THEN 'WEEK3' ELSE INITCAP(week_label) END AS Weeks,
    -- 商品名称的大小写也可以根据需求调整，示例里有小写fruits和大写IRON
    CASE WHEN PRODUCTS = 'FRUITS' THEN LOWER(PRODUCTS) ELSE PRODUCTS END AS Goods
FROM weekly_products
ORDER BY TO_NUMBER(SUBSTR(week_label, 5)); -- 按周数数字排序，避免字符串排序的问题

方案2：按Oracle标准自然周计算 #

如果需要遵循数据库的自然周规则（比如ISO周，周一为一周起始；或者默认周，周日为起始），可以用TO_CHAR函数直接获取周数：

WITH date_range AS (
    SELECT TO_DATE('19/02/2021', 'DD/MM/YYYY') AS start_dt,
           TO_DATE('26/03/2021', 'DD/MM/YYYY') AS end_dt
    FROM DUAL
),
weekly_products AS (
    SELECT 
        PRODUCTS,
        TR_DATE,
        -- 'IW'是ISO周（周一为起始），换成'WW'则是数据库默认周（通常周日起始）
        'week' || TO_CHAR(TR_DATE, 'IW') AS week_label
    FROM DR_TRANSPORT dt
    CROSS JOIN date_range dr
    WHERE dt.SHIPMENT = 'Y'
      AND dt.TR_DATE BETWEEN dr.start_dt AND dr.end_dt
)
SELECT 
    UPPER(week_label) AS Weeks,
    PRODUCTS AS Goods
FROM weekly_products
ORDER BY TO_NUMBER(SUBSTR(week_label, 5));

方案3：完全匹配示例结果的版本 #

看你的示例结果，FRUITS（01/03/2021）在week1，IRON（19/02/2021）在week2，COTTON（26/02/2021）在WEEK3，这看起来是按日期从晚到早给周编号（最近的周为week1）。这种情况下可以用DENSE_RANK()来实现：

WITH date_range AS (
    SELECT TO_DATE('19/02/2021', 'DD/MM/YYYY') AS start_dt,
           TO_DATE('26/03/2021', 'DD/MM/YYYY') AS end_dt
    FROM DUAL
),
weekly_products AS (
    SELECT 
        PRODUCTS,
        TR_DATE,
        -- 按周倒序编号，最近的周是week1
        'week' || DENSE_RANK() OVER(ORDER BY TRUNC(TR_DATE, 'IW') DESC) AS week_label
    FROM DR_TRANSPORT dt
    CROSS JOIN date_range dr
    WHERE dt.SHIPMENT = 'Y'
      AND dt.TR_DATE BETWEEN dr.start_dt AND dr.end_dt
)
SELECT 
    CASE week_label 
        WHEN 'week1' THEN 'week1'
        WHEN 'week2' THEN 'week2'
        WHEN 'week3' THEN 'WEEK3'
    END AS Weeks,
    CASE PRODUCTS 
        WHEN 'FRUITS' THEN 'fruits'
        ELSE PRODUCTS
    END AS Goods
FROM weekly_products
ORDER BY TO_NUMBER(SUBSTR(week_label, 5));

关键细节说明： #

• CTEdate_range：把起止日期单独抽出来，方便后续修改或者替换成应用层传入的绑定变量，更灵活。
• 周数计算：
  • 方案1用CEIL((TR_DATE - start_dt)/7 +1)：计算日期差后向上取整，确保起始日期所在的时间段为第1周。
  • 方案3用DENSE_RANK() OVER(ORDER BY TRUNC(TR_DATE, 'IW') DESC)：按周的起始日期倒序排名，最近的周排第1，刚好匹配你的示例结果。
• 排序逻辑：用TO_NUMBER(SUBSTR(week_label, 5))提取weekN里的数字部分，这样排序是按数字大小来的，不会出现week10排在week2前面的字符串排序问题。
• 大小写适配：示例里有混合大小写，所以用CASE语句来针对性调整，你可以根据实际需求修改这部分。

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