mysql - SQL 查询以识别最新的非事件组合，同时保留事件组合

Question

注意:通过组合，我指的是svc_id & loc_id

如果组合具有状态，我有兴趣找到一组最新的组合，其状态设置为零设置为0，后来又设置为1，那么这个组合应该被忽略!

MySQL 5.6 架构设置:

CREATE TABLE `activity_log` (
  `id` int(11) NOT NULL,
  `prj_id` bigint(20) NOT NULL,
  `svc_id` bigint(20) NOT NULL,
  `loc_id` bigint(20) NOT NULL,
  `status` tinyint(1) NOT NULL,
  `created_by` bigint(20) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_by` bigint(20) DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

INSERT INTO `activity_log` (`id`, `prj_id`, `svc_id`, `loc_id`, `status`, `created_by`, `created_at`, `deleted_by`, `deleted_at`) VALUES
(1, 7751, 7825, 5321, 1, 1, '2018-02-26 06:16:52', NULL, NULL),
(2, 7751, 7821, 5321, 0, 1, '2018-02-26 06:17:05', 1, '2018-02-26 06:17:33'),
(3, 7751, 7821, 5323, 1, 1, '2018-02-26 06:17:05', NULL, NULL),
(4, 7751, 7825, 5324, 0, 1, '2018-02-26 06:16:52', 1, '2018-02-26 06:17:33'),
(5, 7751, 7825, 5325, 1, 1, '2018-02-26 06:16:52', NULL, NULL),
(6, 7751, 7825, 5326, 0, 1, '2018-02-26 06:16:52', 1, '2018-02-26 06:17:33'),
(7, 7751, 7821, 5327, 0, 1, '2018-02-26 06:17:05', 1, '2018-02-26 06:17:33'),
(8, 7751, 7825, 5326, 1, 1, '2018-02-26 06:16:52', NULL, NULL);

---

查询 1:显示所有结果

SELECT * FROM `activity_log`

Results :

| id | prj_id | svc_id | loc_id | status | created_by |           created_at | deleted_by |           deleted_at |
|----|--------|--------|--------|--------|------------|----------------------|------------|----------------------|
|  1 |   7751 |   7825 |   5321 |   true |          1 | 2018-02-26T06:16:52Z |     (null) |               (null) |
|  2 |   7751 |   7821 |   5321 |  false |          1 | 2018-02-26T06:17:05Z |          1 | 2018-02-26T06:17:33Z |
|  3 |   7751 |   7821 |   5323 |   true |          1 | 2018-02-26T06:17:05Z |     (null) |               (null) |
|  4 |   7751 |   7825 |   5324 |  false |          1 | 2018-02-26T06:16:52Z |          1 | 2018-02-26T06:17:33Z |
|  5 |   7751 |   7825 |   5325 |   true |          1 | 2018-02-26T06:16:52Z |     (null) |               (null) |
|  6 |   7751 |   7825 |   5326 |  false |          1 | 2018-02-26T06:16:52Z |          1 | 2018-02-26T06:17:33Z |
|  7 |   7751 |   7821 |   5327 |  false |          1 | 2018-02-26T06:17:05Z |          1 | 2018-02-26T06:17:33Z |
|  8 |   7751 |   7825 |   5326 |   true |          1 | 2018-02-26T06:16:52Z |     (null) |               (null) |

---

查询 2:显示离线组合

SELECT * FROM `activity_log` WHERE `status` = 0 GROUP BY `svc_id`, `loc_id` ORDER BY `id` DESC;

Results :

| id | prj_id | svc_id | loc_id | status | created_by |           created_at | deleted_by |           deleted_at |
|----|--------|--------|--------|--------|------------|----------------------|------------|----------------------|
|  7 |   7751 |   7821 |   5327 |  false |          1 | 2018-02-26T06:17:05Z |          1 | 2018-02-26T06:17:33Z |
|  6 |   7751 |   7825 |   5326 |  false |          1 | 2018-02-26T06:16:52Z |          1 | 2018-02-26T06:17:33Z |
|  4 |   7751 |   7825 |   5324 |  false |          1 | 2018-02-26T06:16:52Z |          1 | 2018-02-26T06:17:33Z |
|  2 |   7751 |   7821 |   5321 |  false |          1 | 2018-02-26T06:17:05Z |          1 | 2018-02-26T06:17:33Z |

预期结果:ID 6 应该被排除，因为它的组合已经在 ID 8 中激活

| id | prj_id | svc_id | loc_id | status | created_by |           created_at | deleted_by |           deleted_at |
|----|--------|--------|--------|--------|------------|----------------------|------------|----------------------|
|  7 |   7751 |   7821 |   5327 |  false |          1 | 2018-02-26T06:17:05Z |          1 | 2018-02-26T06:17:33Z |
|  4 |   7751 |   7825 |   5324 |  false |          1 | 2018-02-26T06:16:52Z |          1 | 2018-02-26T06:17:33Z |
|  2 |   7751 |   7821 |   5321 |  false |          1 | 2018-02-26T06:17:05Z |          1 | 2018-02-26T06:17:33Z |

---

SQL Fiddle Link

Answer 1

CREATE TABLE `activity_log` (
  `id` int(11) NOT NULL,
  `svc_id` bigint(20) NOT NULL,
  `loc_id` bigint(20) NOT NULL,
  `status` tinyint(1) NOT NULL
);

INSERT INTO `activity_log` (`id`, `svc_id`, `loc_id`, `status`) VALUES
(1,  7825, 5321, 1),
(2,  7821, 5321, 0),
(3,  7821, 5323, 1),
(4,  7825, 5324, 0),
(5,  7825, 5325, 1),
(6,  7825, 5326, 0),
(7,  7821, 5327, 0),
(8,  7825, 5326, 1);

SELECT a.* 
  FROM activity_log a 
  JOIN 
     ( SELECT MAX(id) id FROM activity_log GROUP BY svc_id,loc_id) b 
    ON b.id = a.id 
 WHERE a.status = 0;
+----+--------+--------+--------+
| id | svc_id | loc_id | status |
+----+--------+--------+--------+
|  2 |   7821 |   5321 |      0 |
|  4 |   7825 |   5324 |      0 |
|  7 |   7821 |   5327 |      0 |
+----+--------+--------+--------+

编辑:为了证明 cdaiga 解决方案的谬误，考虑这个简化的例子......

DROP TABLE IF EXISTS activity_log;

CREATE TABLE `activity_log` 
(`id` int(11) NOT NULL
,`svc_id` bigint(20) NOT NULL
,`loc_id` bigint(20) NOT NULL
,`status` tinyint(1) NOT NULL
, INDEX(loc_id,svc_id)
);

INSERT INTO `activity_log` (`id`, `svc_id`, `loc_id`, `status`) VALUES
( 1, 5, 1, 1),
( 2, 1, 1, 0),
( 3, 1, 3, 1),
( 4, 5, 4, 0),
( 5, 5, 5, 1),
( 6, 5, 6, 0),
( 7, 1, 7, 0),
( 8, 5, 6, 1),
( 9, 5, 1, 0),
(10, 5, 1, 1),
(11, 1, 1, 0);

正确答案如下:

SELECT a.* 
  FROM activity_log a 
  JOIN 
     ( SELECT MAX(id) id FROM activity_log GROUP BY svc_id,loc_id) b 
    ON b.id = a.id 
 WHERE a.status = 0;

+----+--------+--------+--------+
| id | svc_id | loc_id | status |
+----+--------+--------+--------+
|  4 |      5 |      4 |      0 |
|  7 |      1 |      7 |      0 |
| 11 |      1 |      1 |      0 |
+----+--------+--------+--------+

但是cdaiga的回答可能会给出错误的结果，如下:

SELECT A.*
FROM `activity_log` A
WHERE A.`status` = 0 AND
NOT EXISTS (SELECT null
            FROM `activity_log` B
            WHERE A.`svc_id`=B.`svc_id` AND  A.`loc_id`=B.`loc_id`
            AND B.`status`=1)
GROUP BY `svc_id`, `loc_id`
ORDER BY `id` DESC;
+----+--------+--------+--------+
| id | svc_id | loc_id | status |
+----+--------+--------+--------+
|  7 |      1 |      7 |      0 |
|  4 |      5 |      4 |      0 |
|  2 |      1 |      1 |      0 |
+----+--------+--------+--------+