寻求符合需求的SQL查询：基于Production获取关联运单的所有生产记录

需求说明 #

• 涉及两张表：Cardboard和Production，其中Cardboard表的Cardboard_Number字段最后10位为运单编号。
• 核心需求：
  1. 给定输入的PRODUCTION_NUMBER和POSNR，找到对应生产任务相关的所有运单编号（需包含offset逻辑：该生产时段内的纸板，以及每条生产线上每个匹配纸板前后各1个扫描的纸板）；
  2. 找出所有使用过这些运单编号的Production记录，不受生产时间和生产线限制。

示例数据 #

Production表 #

| PRODUCTION_NUMBER | POSNR |      START_DT      |        END_DT      | PRODUCTION_LINE |
| -----------------:|------:|:-------------------|:-------------------|----------------:|
|       12344       |   1   | 28-FEB-24 14.02.35 | 29-FEB-24 18.02.35 |        2        |
|       12345       |   1   | 29-FEB-24 18.22.40 | 07-MAR-24 18.22.40 |        1        |
|       12345       |   2   | 05-MAR-24 13.02.35 | 12-MAR-24 13.02.35 |        2        |
|       12345       |   2   | 14-MAR-24 14.02.35 | 16-MAR-24 13.02.35 |        2        |

Cardboard表 #

| CARDBOARD_NUMBER |     DATE_TIME      | PRODUCTIONLINE_NUMBER |
| :----------------|:-------------------|----------------------:|
| WDL-005943998-1  | 05-AUG-14 10.03.32 |           1           |
| spL1ml82N4o      | 29-FEB-24 17.13.54 |           1           |
| WDL-005943998-1  | 01-MAR-24 09.44.42 |           1           |
| WDL-005943998-1  | 01-MAR-24 10.34.57 |           1           |
| 950024027237     | 01-MAR-24 10.44.57 |           1           |
| 950024027237     | 01-MAR-24 10.52.57 |           1           |
| WDL-005943998-1  | 01-MAR-24 13.58.43 |           2           |
| WDL-005943998-1  | 01-MAR-24 13.58.46 |           2           |
| spL1ml82N4o      | 01-MAR-24 14.09.43 |           2           |
| WDL-005943998-1  | 12-MAR-24 11.48.36 |           2           |

现有查询 #

查询1：基于运单编号返回带offset的Production记录 #

With Cardboard_offset(cardboard_number, date_time, productionline_number) as (
    select cardboard_number, date_time, productionline_number
    from (
        SELECT cardboard_number, date_time, productionline_number,
        sum(case when Cardboard_Number like CONCAT('%', '05943998-1') then 1 else 0 end) over (
            partition by ProductionLine_Number
            order by date_time
            rows BETWEEN 1 preceding AND 1 following
        ) matches
        from cardboard
    ) table1_with_matches
    where matches > 0
)
SELECT distinct p.production_number,
       p.posnr,
       p.production_line,
       p.start_dt,
       p.end_dt
FROM   production p
       INNER JOIN cardboard_offset c
       ON p.production_line = c.productionline_number
       AND c.date_time BETWEEN p.start_dt AND p.end_dt

查询结果：

| PRODUCTION_NUMBER | POSNR | PRODUCTION_LINE |       START_DT     |       END_DT       |
| -----------------:|------:|----------------:|:-------------------|:-------------------|
| 12345             |   1   |        1        | 29-FEB-24 18.22.40 | 07-MAR-24 18.22.40 |
| 12345             |   2   |        2        | 05-MAR-24 13.02.35 | 12-MAR-24 13.02.35 |

查询2：基于生产任务返回带offset的Cardboard记录 #

select d.cardboard_number, d.date_time, d.productionline_number
from production a
join lateral
(
    select cardboard_number, date_time, productionline_number
    from (
    SELECT cardboard_number, date_time, productionline_number,
        sum(case when date_time between a.start_dt and a.end_dt then 1 else 0 end) over (
            partition by ProductionLine_Number
            order by date_time
            rows BETWEEN 1 preceding AND 1 following
        ) matches
        from cardboard
    ) table1_with_matches
    where matches > 0
) d on a.production_line = d.productionline_number
where a.production_number = 12345 and a.posnr = 1

查询结果：

| CARDBOARD_NUMBER |      DATE_TIME     | PRODUCTIONLINE_NUMBER |
| :----------------|:-------------------|----------------------:|
| spL1ml82N4o      | 29-FEB-24 17.13.54 |           1           |
| WDL-005943998-1  | 01-MAR-24 09.44.42 |           1           |
| WDL-005943998-1  | 01-MAR-24 10.34.57 |           1           |
| 950024027237     | 01-MAR-24 10.44.57 |           1           |
| 950024027237     | 01-MAR-24 10.52.57 |           1           |

预期输出（输入：PRODUCTION_NUMBER=12345，POSNR=1，Offset=1） #

12344 - 1（因该生产使用了运单编号'pL1ml82N4o'）
12345 - 1（因该生产使用了运单编号'05943998-1'）
12345 - 2（因该生产使用了运单编号'05943998-1'）

合并后的高效查询 #

以下是将两个需求合并的SQL查询，步骤分为：

1. 定位目标生产任务的时间范围；
2. 基于offset逻辑筛选关联的纸板记录；
3. 提取这些纸板的运单编号；
4. 关联所有使用过这些运单编号的生产记录。

WITH target_production AS (
    -- 第一步：获取输入生产任务的时间范围和生产线
    SELECT start_dt, end_dt, production_line
    FROM production
    WHERE production_number = '12345' AND posnr = 1
),
offset_cardboards AS (
    -- 第二步：应用offset逻辑，筛选目标生产时段前后各1条的纸板
    SELECT cardboard_number, productionline_number
    FROM (
        SELECT 
            c.cardboard_number,
            c.productionline_number,
            -- 标记目标生产时段内的纸板，计算前后1条的匹配数
            SUM(CASE WHEN c.date_time BETWEEN tp.start_dt AND tp.end_dt THEN 1 ELSE 0 END) OVER (
                PARTITION BY c.productionline_number
                ORDER BY c.date_time
                ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
            ) AS matches
        FROM cardboard c
        CROSS JOIN target_production tp
    ) t
    WHERE matches > 0
),
waybill_numbers AS (
    -- 第三步：提取纸板对应的运单编号（最后10位）
    SELECT DISTINCT SUBSTR(cardboard_number, GREATEST(LENGTH(cardboard_number)-9, 1)) AS waybill_no
    FROM offset_cardboards
)
-- 第四步：找出所有使用过这些运单编号的生产记录
SELECT DISTINCT 
    p.production_number || ' - ' || p.posnr AS production_info,
    '因该生产使用了运单编号''' || w.waybill_no || '''' AS reason
FROM production p
JOIN cardboard c ON p.production_line = c.productionline_number 
    AND c.date_time BETWEEN p.start_dt AND p.end_dt
JOIN waybill_numbers w ON SUBSTR(c.cardboard_number, GREATEST(LENGTH(c.cardboard_number)-9, 1)) = w.waybill_no
ORDER BY p.production_number, p.posnr;

查询结果：

| production_info | reason                                  |
|-----------------|-----------------------------------------|
| 12344 - 1       | 因该生产使用了运单编号'pL1ml82N4o'      |
| 12345 - 1       | 因该生产使用了运单编号'05943998-1'      |
| 12345 - 2       | 因该生产使用了运单编号'05943998-1'      |

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