MySQL:从两个表中查找 "last modified"日期

Question

我有两个 MySQL 表:tech_requests 和 comments。我想在按“上次修改”日期排序的列表中显示每个 tech_request 一次，无论是 tech_request 创建的日期还是与该 tech_request 相关的最新评论。我试图使用 UNION 但我被卡住了。任何想法将不胜感激。以下是表格:

CREATE TABLE `tech_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_member_id` int(3) NOT NULL,
  `date_time` datetime NOT NULL,
  `request` text NOT NULL,
  `building_id` int(2) NOT NULL,
  `technician_id` int(2) DEFAULT NULL,
  `completed` tinyint(1) NOT NULL,
  `subject` varchar(30) NOT NULL DEFAULT '',
  `category_id` int(2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=203 DEFAULT CHARSET=utf8;

CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tech_request_id` int(11) NOT NULL,
  `technician_id` int(2) NOT NULL,
  `date_time` datetime NOT NULL,
  `comment` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=234 DEFAULT CHARSET=utf8;

Answer 1

您正在寻找这样的东西吗？

SELECT r.id, r.staff_member_id, ...,
       GREATEST(r.date_time, COALESCE(c.date_time, 0)) last_modified
  FROM tech_requests r LEFT JOIN 
(
  SELECT tech_request_id, MAX(date_time) date_time
    FROM comments c
   GROUP BY tech_request_id
) c
    ON r.id = c.tech_request_id
 ORDER BY last_modified

这里是SQLFiddle 演示