如何用 SQL 加入最新的上一條記錄

我有一個表，其架構是這樣的：

CREATE TABLE product_shipping(
   product_id CHAR(10),
   product_name CHAR(10),
   deal_dt DATETIME,
   deal_reason_no SMALLINT,
   cancel_dt DATETIME,
   cancel_reason_no SMALLINT
);

一旦安排好發貨日期，將設置 deal_dt 和 deal_reason_no，並且 cancel_dt 和 cancel_reason_no 為空。但是 product_id 在表中不是唯一的，因為每個產品的運輸都可以取消。一旦取消 cancel_dt 和 cancel_reason_no 將被設置。如果取消的產品被其他客戶購買，則運費記錄在新行中，並且可以在新的運費中更改 product_name。

我想查詢其deal_reason_no 為1、3 或5 的product_id 和deal_dt，deal_dt 介於'2014-04-01 00:00:00'and'2014-04-11 00:00:00'和最新的先前deal_dt 和最新的先前cancel_dt 之間，其cancel_reason_no 為2 或4 的product_id。

我認為SQL可能是這樣的：

SELECT 
C.product_id as product_id,
C.deal_dt as deal_dt,
R.deal_dt as previous_deal_dt,
R.cancel_dt as previous_cancel_dt,
FROM product_shipping C
LEFT JOIN product_shipping R ON (???)
WHERE TO_CHAR(C.deal_reason_no ) in ('1', '3', '5')
AND C.deal_dt between '2014-04-01 00:00:00' and '2014-04-11 00:00:00'

但我不確定如何加入以獲得最新的先前記錄。

我正在使用 Informix 11.70。

編輯：添加我的想法。這合理嗎？

SELECT 
C.product_id as product_id,
C.deal_dt as deal_dt,
MAX(R.deal_dt) as previous_deal_dt,
MAX(R.cancel_dt) as previous_cancel_dt,
FROM product_shipping C
LEFT JOIN product_shipping R ON (C.product_id = R.product_id and TO_CHAR(R.cancel_reason_no) MATCHES ('2', '4') and R.cancel_dt <= C.deal_dt)
WHERE TO_CHAR(C.deal_reason_no ) in ('1', '3', '5')
AND C.deal_dt between '2014-04-01 00:00:00' and '2014-04-11 00:00:00'
GROUP BY 1,2

我建議您加入符合條件的先前產品，並（如果存在）縮小到不存在後面行的產品：

SELECT P.product_id AS product_id,
     ,P.deal_dt    AS deal_dt
     ,C.deal_dt    AS previous_deal_dt
     ,C.cancel_dt  AS previous_cancel_dt
FROM   product_shipping p
LEFT   JOIN product_shipping C ON C.product_id = P.product_id
                             AND C.cancel_reason_no IN (2, 4)
                             AND C.cancel_dt < P.deal_dt
WHERE  P.deal_reason_no IN (1, 3, 5)
AND    P.deal_dt BETWEEN '2014-04-01 00:00:00' AND '2014-04-11 00:00:00'
AND    NOT EXISTS (
  SELECT 1
  FROM   product_shipping C1
  WHERE  C1.product_id = C.product_id
  AND    C1.cancel_reason_no IN (2, 4)
  AND    C1.cancel_dt < P.deal_dt
  AND    C1.cancel_dt > C.deal_dt
  );

假設(product_id,cancel_dt)是UNIQUE，否則您需要決勝局標準是明確的。這種方式你不需要GROUP BY，因為只有一個（或沒有）行是左連接的。

還假設deal_dt並且cancel_dt應該來自同一個取消的交易。

使用視窗功能會更簡單，但根據重複文章lag()下的討論，您的版本似乎不支持該功能。

假設您有足夠新的版本，infomix 支持LAG和LEAD功能超過排名操作，請參閱http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1513.htm

這些僅在目前行和您考慮的前一行使用相同的過濾子句找到時才有效，否則您需要引用該表三次：首先找到您感興趣的基本行，下一個加入到該行以查找要考慮的先前行，第三個是左外連接或相關子查詢，如果可能有多個與“前一行”條件匹配，則引用兩者以排除額外行。子查詢或額外連接選項是否比另一個更有效將取決於您的 DBMS 的查詢計劃器和您的搜尋條件，因此我建議您嘗試使用真實的測試數據對它們進行基準測試。

例如：

SELECT <things>
FROM sometable AS current
LEFT OUTER JOIN sometable AS older
ON older.refdate<current.refdate
AND <filters on older to find rows that match the "previous row" criteria>
LEFT OUTER JOIN some table AS middle
ON middle.refdate<current.refdate AND middle.refdate>older.refdate
AND <filters on middle to find rows that match the "previous row" criteria>
WHERE <filters to find current row or rows>
AND middle.refdate IS NULL

在 where 子句而不是 join 子句中對中間進行空檢查意味著任何組合，其中有比“較舊”連接匹配的行更新的行，因此您知道“目前”是目前行和“較舊的”是匹配“前一行”標準的最近行。

如果您知道在“目前”中找到的每一行總是至少有一個前一行，那麼“舊”的連接可以是一個內部連接，它可以讓查詢執行器更有效率。

如果您對目前行和前一行的搜尋條件相同（當然除了相對年齡檢查），那麼使用LAG()應該會更有效，因為您不需要對錶的其他兩個引用中的任何一個。

引用自：https://dba.stackexchange.com/questions/71720