如何根据以下记录删除记录

Serda Shehu

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

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' )

柠檬

您可以在该字段上使用一对窗口函数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子句中的条件。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。