mysql - 从(KEY/VALUE)类型表中查询最后更新

Question

下面是创建表的代码

    CREATE TABLE `updates` (
      `id` int(11) NOT NULL,
      `ticket_id` int(11) NOT NULL,
      `user_id` int(11) NOT NULL,
      `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `updates` (`id`, `ticket_id`, `user_id`, `created_at`) VALUES
    (1, 1, 1, '2017-09-01 10:23:25'),
    (2, 1, 1, '2017-09-01 11:23:25'),
    (3, 1, 1, '2017-09-01 12:23:25'),
    (4, 2, 1, '2017-09-01 10:23:25'),
    (5, 2, 1, '2017-09-01 11:23:25'),
    (6, 2, 1, '2017-09-01 12:23:25');

    CREATE TABLE `updates_value` (
      `id` int(11) NOT NULL,
      `updates_id` int(11) NOT NULL,
      `meta_key` varchar(255) NOT NULL,
      `meta_value` varchar(255) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `updates_value` (`id`, `updates_id`, `meta_key`, `meta_value`) VALUES
    (1, 1, 'comment', NULL),
    (2, 1, 'status', 'open'),
    (3, 2, 'comment', NULL),
    (4, 2, 'status', 'pending'),
    (5, 3, 'comment', 'last comment ticket 1'),
    (6, 3, 'status', 'pending'),
    (7, 4, 'comment', NULL),
    (8, 4, 'status', 'open'),
    (9, 5, 'comment', NULL),
    (10, 5, 'status', 'pending'),
    (11, 6, 'comment', 'last comment ticket 2'),
    (12, 6, 'status', 'pending');


    ALTER TABLE `updates`
      ADD PRIMARY KEY (`id`),
      ADD KEY `user_id` (`user_id`);

    ALTER TABLE `updates_value`
      ADD PRIMARY KEY (`id`);

我有 2 张 table :

updates
-
id     ticket_id     user_id               created_at
 1             1           1      2017-09-01 10:23:25
 2             1           1      2017-09-01 11:23:25
 3             1           1      2017-09-01 12:23:25

 4             2           1      2017-09-01 10:23:25
 5             2           1      2017-09-01 11:23:25
 6             2           1      2017-09-01 12:23:25


updates_value
-
id     update_id    meta_key               meta_value
 1             1     comment                        -
 2             1      status                     open
 3             2     comment                        -
 4             2      status                  pending
 5             3     comment    last comment ticket 1
 6             3      status                  pending

 7             4     comment                        -
 8             4      status                     open
 9             5     comment                        -
10             5      status                  pending
11             6     comment    last comment ticket 2
12             6      status                  pending

现在，我需要创建一个结果列表:

1. 每位用户的所有门票
2. 显示每个状态的最后评论

因此，在上面的示例中，我想要一个结果:

ID:1、5(票证 1)、7、11(票证 2)

RESULTS
-
updates_value_id    ticket_id                  comment
               1            1                        -
               5            1    last comment ticket 1
               7            2                        -
              11            2    last comment ticket 2

预先感谢任何花时间阅读并回答此问题的人! :)

Answer 1

SELECT a.* FROM updates_value a LEFT JOIN (
    SELECT MAX(updates_id) as updates_id 
    FROM updates_value a
    LEFT JOIN updates b ON (a.updates_id=b.id)
    WHERE meta_key = 'status'
    GROUP BY CONCAT(ticket_id,meta_value) 
) b ON a.updates_id=b.updates_id
WHERE b.updates_id is not null and meta_key = 'comment'