可以將此查詢重寫為聯接嗎？

我相當確定可以將其重寫為連接查詢，但我只是不確定如何處理它：

SELECT 
  events.*, 
  (SELECT MAX(updated_at) 
   FROM   events e 
   WHERE  e.user_id = events.user_id 
          AND type = 'follow' 
          AND e.updated_at < events.updated_at 
          AND events.type = 'unfollow'
  ) AS last_follow_date 
FROM   events 
WHERE  user_id = 1; 

數據庫結構：

CREATE TABLE `events` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `created_at` timestamp NULL DEFAULT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   `user_id` int(10) unsigned NOT NULL,
   `type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
   `follower` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
   `follower_pk` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
   PRIMARY KEY (`id`),
   KEY `events_user_id_foreign` (`user_id`),
   CONSTRAINT `events_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
   `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

一些數據：

INSERT INTO `users` (`id`, `name`, `email`)
VALUES (1, 'Aydin Hassan', 'email@website.com');

INSERT INTO `events` (`id`, `created_at`, `updated_at`, `user_id`, `type`, `follower`, `follower_pk`)
VALUES 
(1, '2015-01-01 00:00:00', '2015-01-01 00:00:00', 1, 'follow', 'annie.leibovitz', '10'),
(2, '2015-04-04 00:00:00', '2015-04-04 00:00:00', 1, 'unfollow', 'annie.leibovitz', '10'),
(3, '2015-04-04 00:00:00', '2015-04-04 00:00:00', 1, 'follow', 'edward.weston', '11'),
(5, '2015-10-10 00:00:00', '2015-10-10 00:00:00', 1, 'follow', 'annie.leibovitz', '10'),
(4, '2017-01-01 10:10:00', '2017-01-01 10:10:00', 1, 'unfollow', 'annie.leibovitz', '10');

該表儲存有關臨時社交平台上使用者的關注者的事件。查詢正在添加（如果事件是取消關注類型）關注者開始關注使用者的最後日期。

正確的結果應該是：

+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+
| id |     created_at      |     updated_at      | user_id |   type   |    follower     | follower_pk |  last_follow_date   |
+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+
|  1 | 2015-01-01 00:00:00 | 2015-01-01 00:00:00 |       1 | follow   | annie.leibovitz |          10 | NULL                |
|  2 | 2015-04-04 00:00:00 | 2015-04-04 00:00:00 |       1 | unfollow | annie.leibovitz |          10 | 2015-01-01 00:00:00 |
|  3 | 2015-04-04 00:00:00 | 2015-04-04 00:00:00 |       1 | follow   | edward.weston   |          11 | NULL                |
|  4 | 2017-01-01 10:10:00 | 2017-01-01 10:10:00 |       1 | unfollow | annie.leibovitz |          10 | 2015-10-10 00:00:00 |
|  5 | 2015-10-10 00:00:00 | 2015-10-10 00:00:00 |       1 | follow   | annie.leibovitz |          10 | NULL                |
+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+

作為一個額外的問題：是否有更好的方法來儲存和查詢這些數據？

您可以使用以下JOINed 查詢獲得所需的內容，該查詢與您的原始查詢非常相似：

SELECT
   e.id, e.created_at, e.updated_at, e.user_id, e.type, e.follower, e.follower_pk, 
   max(e2.updated_at) AS last_follow_date
FROM
   events e
   LEFT JOIN events e2 
       ON      e2.user_id = e.user_id 
           AND e2.type = 'follow' 
           AND e2.updated_at < e.updated_at
           AND e.type = 'unfollow' 
WHERE
   e.user_id = 1
GROUP BY
   e.id 
ORDER BY
   e.id, e.updated_at ;

關於性能，您的查詢將受益於索引，例如：

-- Index to improve performance
CREATE INDEX idx_events_user_id_type_updated_at
   ON events (user_id, type, updated_at) ;

三列user_id,type並updated_at出現在JOIN條件中。您想要它們的順序是user_id（您進行 = 比較，並且應該具有高基數 - 即：非常多的值 -），type（您進行 = 比較，基數 = 2），updated_at檢查不等式。 .. MySQL 不足以將其用作索引查找的一部分，但會將索引設為covering一個，這樣可以避免檢查原始表。

---

替代方案：對原始查詢稍作改動，可能會節省一些時間：

SELECT 
  e1.*, 
  case when type = 'unfollow' then
     (SELECT MAX(e2.updated_at) 
      FROM   events e2 
      WHERE  
                 e2.user_id = e1.user_id 
             AND e2.type = 'follow'
             AND e2.updated_at < e1.updated_at
      )
  else NULL end AS last_follow_date
FROM   events e1
WHERE  e1.user_id = 1; 

你可以在*這裡*查看dbfiddle的所有內容

引用自：https://dba.stackexchange.com/questions/179407