我有一個有列的表
Car | User | Location | Time | Type
我想洗掉所有重復的行，只留下基于car, user, location, time列不同的行
例如：
Car | User | Location | Time | Type
-----------------------------------------------------------------
1 | Ben | Chicago | 2022-02-12 03:12:45 | OUT //should be deleted
-----------------------------------------------------------------
1 | Ben | Chicago | 2022-02-12 03:12:45 | IN //should be deleted
-----------------------------------------------------------------
2 | Sam | New York | 2022-02-12 04:42:45 | OUT //should be kept
-----------------------------------------------------------------
1 | Ben | Chicago | 2022-02-12 04:50:00 | OUT //should be kept
為了更容易獲得幫助
CREATE TABLE rent_logs (
car varchar(30),
user varchar(30),
location varchar(30),
time datetime,
type varchar(10)
);
INSERT INTO rent_logs ( car, user, location, time, type )
VALUES ( 1, 'Ben', 'Chicago','2022-02-12 03:12:45', 'OUT' ),
( 1, 'Ben', 'Chicago',' 2022-02-12 03:12:45', 'IN' ),
( 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熱心網友回復：
將表加入到一個查詢中，該查詢聚合rent_logs并回傳所有重復的行：
DELETE r
FROM rent_logs r
INNER JOIN (
SELECT car, user, location, time
FROM rent_logs
GROUP BY car, user, location, time
HAVING COUNT(*) > 1
) t ON (t.car, t.user, t.location, t.time) = (r.car, r.user, r.location, r.time);
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/474788.html