Oracle SQL -在交付行之后选择第一个检查行

Question

我正在努力解决以下问题。

tblTrans中的数据如下：

| Transaction_ID |    Transaction_Type   | Hours | Employee_ID |
|:--------------:|:---------------------:|:-----:|:-----------:|
|       107      |   In-Place Delivery   |  0.60 |     SDK     |
|       110      |   In-Place Delivery   |  0.88 |     SDK     |
|       112      |       Inspection      |  1.22 |     SDK     |
|       114      |   In-Place Delivery   |  2.11 |     JMK     |
|       115      |       Inspection      |  0.01 |     SDK     |
|       116      |       Inspection      |  0.64 |     JMK     |
|       239      | Out-of-Place Delivery |  0.12 |     JMK     |
|       241      |   In-Place Delivery   |  0.33 |     JMK     |
|       255      | Out-of-Place Delivery |  0.87 |     KWE     |
|       256      |       Inspection      |  5.90 |     JMK     |
|       263      |       Inspection      | 11.80 |     SDK     |
|       291      |   In-Place-Delivery   |  1.00 |     SDK     |
|       292      |       Inspection      |  0.04 |     JMK     |
|       400      | Out-of-Place Delivery |  9.50 |     JMK     |
|       401      |       Inspection      |  1.21 |     JMK     |

我试图完成的是在每次就地交付之后确定第一次检查事务，创建一个表，如下所示：

| Delivery_Transaction_ID | First_Inspection |
|:-----------------------:|:----------------:|
|           107           |        112       |
|           110           |        112       |
|           114           |        115       |
|           241           |        256       |
|           291           |        292       |

对于即将到来的检验，交付后的Transaction_ID总是大于以前交付的。一切按顺序进行。但是，它可能不一定是+1，因为有时系统会跳转数字。然而，它将永远是更大的。

到目前为止，我已经尝试了以下查询的几个变体：

WITH
  In_Place_Deliveries AS (  
      SELECT
        Transaction_ID AS Delivery_Transaction    
      FROM
        tblTrans    
      WHERE
        Transaction_Type = 'In-Place Delivery'  
  ),


  SELECT
    ipd.Delivery_Transaction,
    LEAD(MIN(Transaction_ID)) OVER (ORDER BY Transaction_Type) AS "First_Inspection"
  FROM
    tblTrans
      INNER JOIN In_Place_Deliveries ipd on ipd.Delivery_Transaction = tblTrans.Transaction_ID

但我明白：

| DELIVERY_TRANSACTION | First_Inspection |
|:--------------------:|:----------------:|
|          107         |        110       |
|          110         |        114       |
|          114         |        241       |
|          241         |      (null)      |

这显然是不正确的。

为了演示，我在这里设置了一个SQL小提琴，包括数据和查询。

如何重新设计查询以实现所需的输出？

Answer 1

只需使用铅忽略NULLS：

WITH In_Place_Deliveries AS 
 (  
      SELECT
        Transaction_ID AS Delivery_Transaction ,
        Transaction_Type,
        -- next Inspection
        lead(case when Transaction_Type = 'Inspection' then Transaction_ID end ignore nulls)
        OVER (ORDER BY Transaction_ID)
      FROM
        tblTrans    
      WHERE
        Transaction_Type IN ( 'In-Place Delivery', 'Inspection')
  )
SELECT *
FROM In_Place_Deliveries
WHERE Transaction_Type = 'In-Place Delivery'

请参阅小提琴