如何根據以下記錄洗掉記錄

我有一個包含以下列的表格

Car   |   User  |  Location  |  Time  |  Type

型別可以是

'OUT' if the user's request to rent the car is accepted
'IN' when the user stops using the car and registers it back into the system
'DENIED' when the access to rent the car is denied

所以關于型別的記錄順序應該是OUT-IN（時間升序）

存在不一致的資料，其中可能有幾行具有相同的汽車、用戶、位置和型別值，如下所示

Car   |   User  |  Location  |         Time          |  Type
-----------------------------------------------------------------
  1   |    Ben  |   Chicago  |  2022-02-12 03:12:45  |   OUT       //should be deleted
-----------------------------------------------------------------
  1   |    Ben  |   Chicago  |  2022-02-12 04:12:45  |   OUT      //should be deleted
-----------------------------------------------------------------
  2   |    Sam  |  New York  |  2022-02-12 04:42:45  |   OUT
-----------------------------------------------------------------
  1   |    Ben  |   Chicago  |  2022-02-12 04:50:00  |   OUT         //should be kept   
-----------------------------------------------------------------
  1   |    Sam  |  New York  |  2022-02-12 07:32:12  |   IN
-----------------------------------------------------------------
  1   |    Ben  |   Chicago  |  2022-02-12 08:18:45  |   IN         //should be deleted
-----------------------------------------------------------------
  3   |    Mia  | San Franc  |  2022-02-12 09:12:43  |   OUT
-----------------------------------------------------------------
  1   |    Ben  |   Chicago  |  2022-02-12 09:27:23  |   IN            //should be kept    

因此，只應保留最后的 OUT 和 IN 記錄，而應洗掉其他重復項。

為了更容易獲得幫助

CREATE TABLE rent_logs (
car varchar(30),
user varchar(30),
location varchar(30),
time datetime
type varchar(10)

);

INSERT INTO rent_logs ( car, username, location, time, type ) VALUES
( 1, 'Ben', 'Chicago','2022-02-12 03:12:45',  'OUT' ), ( 1, 'Ben', 'Chicago',' 2022-02-12 04:12:45  ',  'OUT' ), ( 2, 'Sam', 'New york','2022-02-12 04:42:45',  'OUT' ), ( 1, 'Ben', 'Chicago','2022-02-12 04:50:00',  'OUT' ), ( 2, 'Sam', 'New york','2022-02-12 07:32:12 ',  'IN' ), ( 1, 'Ben', 'Chicago','22022-02-12 08:18:45',  'IN' ), ( 3, 'Mia', 'Chicago','2022-02-12 09:12:43',  'OUT' ), ( 1, 'Ben', 'Chicago','2022-02-12 09:27:23',  'IN' )

uj5u.com熱心網友回復：

您可以在欄位上使用一對視窗函式Type：

• LAG, 檢索前一個值
• LEAD, 檢索下一個值

這些視窗函式將根據涉及以下欄位的特定磁區按此順序應用：User、Car、Location。

獲得這些值后，您可以在滿足以下欄位條件之一時檢索行Type：

• Type = OUT和TypeNext = IN（最后一個OUT）
• Type = IN和TypePrev = IN而不是TypeNext = IN（最后一個IN）
• Type = DENIED

如果你對近行有更復雜的條件，你可以通過這三個計算值來處理它們。

WITH cte AS (
    SELECT Car,
           User,
           Location,
           Time,
           LAG(Type) OVER(
               PARTITION BY User, Car, Location 
               ORDER BY Time)                    AS TypePrev,
           Type,
           LEAD(Type) OVER(
               PARTITION BY User, Car, Location 
               ORDER BY Time)                    AS TypeNext
    FROM rent_logs 
)
SELECT Car,
       User,
       Location,
       Time,
       Type
FROM cte
WHERE (Type = 'OUT' AND TypeNext = 'IN') 
   OR (Type = 'IN'  AND TypePrev = 'IN' AND (TypeNext = 'OUT' OR TypeNext IS NULL))
   OR (TypePrev IS NULL AND TypeNext IS NULL)
   OR (Type = 'DENIED')
ORDER BY User, 
         Time

在此處檢查 SQL Fiddle 。

如果您想稍微了解一下它的作業原理以及小提琴鏈接中的中間結果，我建議從注釋行中洗掉注釋并注釋WHERE子句中的條件。

標籤：mysql sql 数据库

上一篇：如果插入不成功，避免增加主鍵？

下一篇：PDOMYSQLHY093（引數數量錯誤）發出錯誤