MySQL 来自多个表的最新相关记录

Question

假设一个主“作业”表和两个相应的“日志”表(一个用于服务器事件，另一个用于用户事件，每个表中存储的数据截然不同)。

从两个“日志”表(如果有)中的每一个返回选择的“作业”记录和最新的相应日志记录(具有多个字段)的最佳方法是什么。

确实从以下方面获得了一些灵感:MySQL Order before Group by

以下 SQL 将创建一些示例表/数据...

CREATE TABLE job (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` tinytext NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE job_log_server (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `job_id` int(11) NOT NULL,
    `event` tinytext NOT NULL,
    `ip` tinytext NOT NULL,
    `created` datetime NOT NULL,
    PRIMARY KEY (id),
    KEY job_id (job_id)
);

CREATE TABLE job_log_user (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `job_id` int(11) NOT NULL,
    `event` tinytext NOT NULL,
    `user_id` int(11) NOT NULL,
    `created` datetime NOT NULL,
    PRIMARY KEY (id),
    KEY job_id (job_id)
);

INSERT INTO job VALUES (1, 'Job A');
INSERT INTO job VALUES (2, 'Job B');
INSERT INTO job VALUES (3, 'Job C');
INSERT INTO job VALUES (4, 'Job D');

INSERT INTO job_log_server VALUES (1, 2, 'Job B Event 1', '127.0.0.1', '2000-01-01 00:00:01');
INSERT INTO job_log_server VALUES (2, 2, 'Job B Event 2', '127.0.0.1', '2000-01-01 00:00:02');
INSERT INTO job_log_server VALUES (3, 2, 'Job B Event 3*', '127.0.0.1', '2000-01-01 00:00:03');
INSERT INTO job_log_server VALUES (4, 3, 'Job C Event 1*', '127.0.0.1', '2000-01-01 00:00:04');

INSERT INTO job_log_user VALUES (1, 1, 'Job A Event 1', 5, '2000-01-01 00:00:01');
INSERT INTO job_log_user VALUES (2, 1, 'Job A Event 2*', 5, '2000-01-01 00:00:02');
INSERT INTO job_log_user VALUES (3, 2, 'Job B Event 1*', 5, '2000-01-01 00:00:03');
INSERT INTO job_log_user VALUES (4, 4, 'Job D Event 1', 5, '2000-01-01 00:00:04');
INSERT INTO job_log_user VALUES (5, 4, 'Job D Event 2', 5, '2000-01-01 00:00:05');
INSERT INTO job_log_user VALUES (6, 4, 'Job D Event 3*', 5, '2000-01-01 00:00:06');

一个选项(仅从每个表返回 1 个字段)是使用嵌套子查询...但 ORDER BY 必须在 GROUP BY (x2) 的单独查询中完成:

SELECT
    *
FROM
    (
        SELECT
            s2.*,
            jlu.event AS user_event
        FROM
            (
                SELECT
                    *
                FROM
                    (
                        SELECT
                            j.id,
                            j.name,
                            jls.event AS server_event
                        FROM
                            job AS j
                        LEFT JOIN
                            job_log_server AS jls ON jls.job_id = j.id
                        ORDER BY
                            jls.created DESC
                    ) AS s1
                GROUP BY
                    s1.id
            ) AS s2
        LEFT JOIN
            job_log_user AS jlu ON jlu.job_id = s2.id
        ORDER BY
            jlu.created DESC
    ) AS s3
GROUP BY
    s3.id;

实际上似乎表现得很好......只是不太容易理解。

或者您可以尝试在两个单独的子查询中返回日志记录并对其进行排序:

SELECT
    j.id,
    j.name,
    jls2.event AS server_event,
    jlu2.event AS user_event
FROM
    job AS j
LEFT JOIN
    (
        SELECT
            jls.job_id,
            jls.event
        FROM
            job_log_server AS jls
        ORDER BY
            jls.created DESC
    ) AS jls2 ON jls2.job_id = j.id
LEFT JOIN
    (
        SELECT
            jlu.job_id,
            jlu.event
        FROM
            job_log_user AS jlu
        ORDER BY
            jlu.created DESC
    ) AS jlu2 ON jlu2.job_id = j.id
GROUP BY
    j.id;

但这似乎需要相当长的时间才能运行...可能是因为它添加到临时表中的记录量很大，然后这些记录大多被忽略(为了保持简短，我没有添加任何记录)作业表的条件，否则只会返回事件作业)。

不确定我是否错过了任何明显的事情。

Answer 1

下面的SQL Fiddle怎么样？ 。它产生与您的两个查询相同的结果。

SELECT j.id, j.name, 
( 
  SELECT s.event 
  FROM job_log_server s
  WHERE j.id = s.job_id
  ORDER BY s.id DESC
  LIMIT 1
)AS SERVER_EVENT,
( 
  SELECT u.event 
  FROM job_log_user u
  WHERE j.id = u.job_id
  ORDER BY u.id DESC
  LIMIT 1
)AS USER_EVENT
FROM job j

编辑 SQL Fiddle :

SELECT m.id, m.name, js.event AS SERVER_EVENT, ju.event AS USER_EVENT
FROM 
(
  SELECT j.id, j.name,
  ( 
    SELECT s.id
    FROM job_log_server s
    WHERE j.id = s.job_id
    ORDER BY s.id DESC
    LIMIT 1
  )AS S_E,
  ( 
    SELECT u.id
    FROM job_log_user u
    WHERE j.id = u.job_id
    ORDER BY u.id DESC
    LIMIT 1
  )AS U_E
  FROM job j
) m
LEFT JOIN job_log_server js ON js.id = m.S_E
LEFT JOIN job_log_user ju ON ju.id = m.U_E