php - 获取父工单的用户 ID - MySQL

Question

我有一个查询，可以获取按工单 ID 分组的所有最新工单数据。

SELECT t1.*
FROM cq_tickets t1 
JOIN (
    SELECT ticket_id, MAX(date_updated) AS date_updated 
    FROM cq_tickets 
    GROUP BY ticket_id
) a 
ON t1.ticket_id = a.ticket_id AND t1.date_updated = a.date_updated 
WHERE current_editing_agent IS NULL AND status != 'closed'

但我需要获取父工单的用户 ID，这样我还可以显示该工单属于哪个客户。

这是我需要的:

但目前，我为 user_id 得到的都是 15 - 这是最新工单数据的用户 ID。

我知道我可以通过运行一个循环并获取父票证为空的用户 ID 来简单地做到这一点，但我只想使用一个查询，因为我使用服务器端 DataTable 中的数据。

我也考虑过执行UNION ALL，但我需要从第一个SELECT 中获取ticket_id。然后在我得到提交工单的客户的用户 ID 后，我将得到他的名字并将其添加到列表中。这可能吗？

编辑:这是我的示例架构:http://sqlfiddle.com/#!9/8f084b/2

CREATE TABLE `cq_tickets` (
  `id` int(11) UNSIGNED NOT NULL,
  `parent_ticket` int(11) UNSIGNED DEFAULT NULL,
  `ticket_id` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `title` text COLLATE utf8mb4_general_ci NOT NULL,
  `message` text COLLATE utf8mb4_general_ci NOT NULL,
  `status` text COLLATE utf8mb4_general_ci NOT NULL,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` enum('customer','agent') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'customer',
  `current_editing_agent` int(11) UNSIGNED DEFAULT NULL,
  `latest_agent_answered` int(11) UNSIGNED DEFAULT NULL
);

INSERT INTO `cq_tickets` (`id`, `parent_ticket`, `ticket_id`, `user_id`, `title`, `message`, `status`, `date_created`, `date_updated`, `created_by`, `current_editing_agent`, `latest_agent_answered`) VALUES
(26, NULL, '00410', 85, 'Another Issue', 'Hello! I\'m back!', 'waiting_for_customer', '2019-02-05 22:06:59', '2019-02-09 00:37:40', 'customer', 15, 15),
(27, 26, '00410', 15, 'Reply to Ticket #00410', 'It\'s good to have you back!', 'waiting_for_customer', '2019-02-05 22:11:16', '2019-02-05 22:11:16', 'agent', NULL, NULL),
(28, 26, '00410', 85, 'Reply to Another Issue', 'I know right? I\'m here!', 'waiting_for_agent', '2019-02-06 11:21:30', '2019-02-06 11:21:39', 'customer', 15, NULL),
(29, 28, '00410', 15, 'Hello World', 'I\'m excited to talk to you.', 'waiting_for_customer', '2019-02-06 11:22:06', '2019-02-06 11:22:06', 'agent', NULL, NULL),
(30, 26, '00410', 85, 'Reply to Another Issue', 'Okay then.', 'waiting_for_agent', '2019-02-06 11:32:45', '2019-02-06 11:32:51', 'customer', 15, NULL),
(31, 30, '00410', 15, 'Reply to Ticket #00410', 'I\'m checking if apostrophe will make it right this time.', 'waiting_for_customer', '2019-02-06 11:33:11', '2019-02-06 11:33:11', 'agent', NULL, NULL),
(32, 26, '00410', 85, 'Reply to Another Issue', 'Noted', 'waiting_for_agent', '2019-02-06 11:34:40', '2019-02-06 11:34:47', 'customer', 15, NULL),
(33, 32, '00410', 15, 'Reply to Ticket #00410', 'I\'m sorry if I\'m persistent.', 'waiting_for_customer', '2019-02-06 11:35:02', '2019-02-06 11:35:02', 'agent', NULL, NULL),
(34, 26, '00410', 85, 'Reply to Another Issue', 'No worries.', 'waiting_for_agent', '2019-02-06 11:40:20', '2019-02-06 11:40:26', 'customer', 15, NULL),
(35, 34, '00410', 15, 'Reply to Ticket #00410', 'Let\'s try again.', 'waiting_for_customer', '2019-02-06 11:40:32', '2019-02-06 11:40:32', 'agent', NULL, NULL),
(36, 26, '00410', 85, 'Reply to Another Issue', 'Try again.', 'waiting_for_agent', '2019-02-06 11:45:25', '2019-02-06 11:45:32', 'customer', 15, NULL),
(37, 36, '00410', 15, 'Reply to Ticket #00410', 'Let\'s do this!', 'waiting_for_customer', '2019-02-06 11:45:39', '2019-02-06 11:45:39', 'agent', NULL, NULL),
(39, 26, '00410', 85, 'Reply to Another Issue', 'Any update?', 'waiting_for_agent', '2019-02-06 11:56:03', '2019-02-06 11:56:18', 'customer', 15, NULL),
(40, 39, '00410', 15, 'Reply to Ticket #00410', 'Please give me more time. Let\'s try again.', 'waiting_for_customer', '2019-02-06 11:56:38', '2019-02-06 11:56:38', 'agent', NULL, NULL),
(41, 26, '00410', 85, 'Reply to Another Issue', 'Not working.', 'waiting_for_agent', '2019-02-06 12:01:47', '2019-02-06 12:01:56', 'customer', 15, NULL),
(42, 41, '00410', 15, 'Reply to Ticket #00410', 'Yep, it\'s still not working.', 'waiting_for_customer', '2019-02-06 12:02:06', '2019-02-06 12:02:06', 'agent', NULL, NULL),
(43, 26, '00410', 85, 'Reply to Another Issue', 'Let\'s do another test.', 'waiting_for_agent', '2019-02-06 15:56:13', '2019-02-06 15:56:33', 'customer', 15, NULL),
(44, 43, '00410', 15, 'Reply to Ticket #00410', 'Let\'s do another test!', 'waiting_for_customer', '2019-02-06 15:56:42', '2019-02-06 15:56:42', 'agent', NULL, NULL),
(51, 44, '00410', 85, 'Reply to Ticket #00410', 'Hello there', 'waiting_for_agent', '2019-02-09 00:19:09', '2019-02-09 00:37:35', 'customer', 15, NULL),
(53, 51, '00410', 15, 'Reply to Ticket #00410', 'I replied!', 'waiting_for_customer', '2019-02-09 00:37:40', '2019-02-09 00:37:40', 'agent', NULL, NULL);

SELECT t1.*
  FROM cq_tickets t1
  JOIN 
     ( SELECT ticket_id
            , MAX(date_updated) AS date_updated
         FROM cq_tickets
        GROUP 
           BY ticket_id
     ) a
    ON t1.ticket_id = a.ticket_id 
   AND t1.date_updated = a.date_updated
 WHERE current_editing_agent IS NULL 
   AND status != 'closed';
+----+---------------+-----------+---------+------------------------+------------+----------------------+---------------------+---------------------+------------+-----------------------+-----------------------+
| id | parent_ticket | ticket_id | user_id | title                  | message    | status               | date_created      | date_updated        | created_by | current_editing_agent | latest_agent_answered |
+----+---------------+-----------+---------+------------------------+------------+----------------------+---------------------+---------------------+------------+-----------------------+-----------------------+
| 53 |            51 | 00410     |      15 | Reply to Ticket #00410 | I replied! | waiting_for_customer | 2019-02-09 00:37:40 | 2019-02-09 00:37:40 | agent      |                  NULL |                  NULL |
+----+---------------+-----------+---------+------------------------+------------+----------------------+---------------------+---------------------+------------+-----------------------+-----------------------+
1 row in set (0.06 sec)

非常感谢任何帮助。谢谢!

Answer 1

我发誓我之前尝试过两次这个查询，但它没有用，所以我转向这里。然后我又试了一次，现在成功了。

这是更新后的架构:http://sqlfiddle.com/#!9/8f084b/4

SELECT t1.`id`, t1.`parent_ticket`, t1.`ticket_id`, (SELECT user_id 
FROM cq_tickets WHERE parent_ticket IS NULL AND ticket_id = t1.ticket_id) AS `user_id`, t1.`title`, t1.`message`, t1.`status`, t1.`date_created`, t1.`date_updated`, t1.`created_by`, t1.`current_editing_agent`, t1.`latest_agent_answered`
FROM cq_tickets t1 
JOIN (
    SELECT ticket_id, MAX(date_updated) AS date_updated 
    FROM cq_tickets 
    GROUP BY ticket_id
) a 
ON t1.ticket_id = a.ticket_id AND t1.date_updated = a.date_updated 
WHERE current_editing_agent IS NULL AND status != 'closed';