mysql - 如何删除有条件的记录

Question

这是我的表结构:

CREATE TABLE `order`
    (`order_id` int, `order_status_id` int, `ip` varchar(11), `date_added` datetime)
;

INSERT INTO `order`
    (`order_id`, `order_status_id`, `ip`, `date_added`)
VALUES
    (1, 0, '192.168.1.1', '2016-12-07 00:00:00'),
    (2, 0, '192.168.1.1', '2016-12-07 00:00:00'),
    (3, 0, '192.168.1.1', '2016-12-07 00:00:00'),
    (4, 0, '192.168.1.1', '2016-12-07 00:00:00'),
    (5, 1, '192.168.1.1', '2016-12-07 00:00:00'),

    (6, 0, '192.168.1.2', '2016-12-08 00:00:00'),
    (7, 0, '192.168.1.2', '2016-12-08 00:00:00'),
    (8, 0, '192.168.1.2', '2016-12-08 00:00:00'),
    (9, 0, '192.168.1.2', '2016-12-08 00:00:00'),
    (10, 1, '192.168.1.2', '2016-12-08 00:00:00'),

    (11, 0, '192.168.1.3', '2016-12-09 00:00:00'),
    (12, 0, '192.168.1.3', '2016-12-09 00:00:00'),
    (13, 0, '192.168.1.3', '2016-12-09 00:00:00'),
    (14, 0, '192.168.1.3', '2016-12-09 00:00:00'),
    (15, 0, '192.168.1.3', '2016-12-09 00:00:00');

http://sqlfiddle.com/#!9/20c0f

我希望 sql 删除所有记录，除了 order_id 为:5、10、15 的记录

说明:我想删除 order_status_id = 0 的记录，条件是:

a) 如果其中一条记录(来自同一 ip/date_added order_id)的 order_status_id = 1，则删除所有 order_status_id = 0 的记录(来自同一 ip/date_added)。在我的示例中，记录 1-4 和 6-9 应该被删除。

b) 如果没有来自相同 ip/date_added 且 order_id = 1 的记录(所有记录的 order_status_id = 0)，则保留一个具有最高 order_id 的记录(所有其他记录均被删除)。在我的示例中，记录 11-14 应该被删除。

Answer 1

SQL DEMO

首先你需要分开你的条件。您有 {ip,date} 和 order_status_id {0,1}

-- first filter
SELECT `ip`, `date_added`
FROM `order`
GROUP BY `ip`, `date_added`
HAVING MAX(`order_status_id`) = 1;

-- second filter    
SELECT `ip`, `date_added`
FROM `order`
GROUP BY `ip`, `date_added`
HAVING MAX(`order_status_id`) = 0;

先删除您删除了所有与第一个过滤器匹配的人，但只删除了 order_status_id = 0 的人，这意味着您留下了 1

DELETE o
FROM `order` o
INNER JOIN (
    SELECT `ip`, `date_added`
    FROM `order`
    GROUP BY `ip`, `date_added`
    HAVING MAX(`order_status_id`) = 1
) filter
  ON o.`ip` = filter.`ip`
 AND o.`date_added` = filter.`date_added`
WHERE o.`order_status_id` = 0;

二次删除你删除所有匹配第二个过滤器的人，但留下没有比他更高的 id 的人。

DELETE o1
FROM `order` o1
INNER JOIN (
    SELECT `ip`, `date_added`
    FROM `order`
    GROUP BY `ip`, `date_added`
    HAVING MAX(`order_status_id`) = 0
) filter
  ON o1.`ip` = filter.`ip`
 AND o1.`date_added` = filter.`date_added`
LEFT JOIN `order` o2
  ON o1.`order_id` < o2.`order_id`
WHERE o2.`ip` IS NOT NULL;

输出