mysql - 如何获取线程 SQL 查询中的最后一条消息

Question

我有两个表，listhdthreads 和 listhdmessages。 listhdthreads 是父表，每个线程都有消息作为子表。

CREATE TABLE `listhdthreads` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `fromUser` int(11) NOT NULL,
  `respondingUser` int(11) DEFAULT NULL,
  `attachments` varchar(300) DEFAULT NULL,
  `customerId` int(11) NOT NULL,
  `locationId` int(11) DEFAULT NULL,
  `areaId` int(11) DEFAULT NULL,
  `assetId` int(11) DEFAULT NULL,
  `projectId` int(11) DEFAULT NULL,
  `contactId` int(11) DEFAULT NULL,
  `title` int(11) NOT NULL,
  `priority` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `createdAt` datetime NOT NULL,
  `ddGroup` int(1) DEFAULT NULL,
  PRIMARY KEY (`idx`)
)

和

CREATE TABLE `listhdmessages` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `parentThreadId` int(11) NOT NULL,
  `createdAt` datetime NOT NULL,
  `sendingUser` int(11) NOT NULL,
  `text` varchar(300) NOT NULL,
  `adminMessage` bit(1) DEFAULT b'0',
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`idx`),
  KEY `parentId` (`parentThreadId`),
  CONSTRAINT `listhdmessages_ibfk_1` FOREIGN KEY (`parentThreadId`) REFERENCES `listhdthreads` (`idx`) ON DELETE CASCADE ON UPDATE CASCADE
)

现在我有这个当前查询，它用所有线程填充一个表格，我想包括上次发送消息的时间(我已经有了)，以及最后一条消息本身的内容(这是我需要的)帮助)。

当前的查询是这样的(删除了选定的不必要的列)

SELECT MAX(m.createdAt) as latestMessage,
FROM listhdthreads as hd 
LEFT JOIN listhdmessages m ON hd.idx = m.parentThreadId 
LEFT JOIN listcustomers c ON hd.customerId = c.idx 
LEFT JOIN listlocations l ON hd.locationId = l.idx 
LEFT JOIN listprojects lp ON hd.projectId = lp.idx
LEFT JOIN listusers lu ON hd.contactId = lu.idx 
LEFT JOIN listusers lus ON hd.fromUser = lus.idx 
LEFT JOIN listusers lusu ON hd.respondingUser = lusu.idx
LEFT JOIN deschdtitles t ON hd.title = t.idx 
LEFT JOIN deschdpriorities p ON hd.priority = p.idx 
LEFT JOIN deschdstatus s ON hd.status = s.idx 
LEFT JOIN helpmessageparticipants hmp ON hd.idx = hmp.parentThreadId
GROUP BY hd.idx

我对获取最新消息本身感到不知所措。我该怎么办呢？我使用的是 MySQL 5.6。

编辑:抱歉，我忘记包含查询其余部分正常工作所需的组。这就是让我头疼的地方。

编辑2:弄清楚了。将我的初始查询变成一个子查询，我只是以这样的方式排序，即每个线程的顶行正是我想要的，然后在其外部进行分组。

SELECT * FROM
(SELECT hd.idx, hd.ddGroup, c.name as customer, IF(hd.locationId=-1,"None",l.name) as location, CONCAT(lu.firstname, ' ', lu.lastname) as contactId, t.title, lp.number, 
UPPER(CONCAT(SUBSTRING(lus.firstname,1,1), SUBSTRING(lus.lastname,1,1))) as createdBy, IF(hd.respondingUser=-1,"---",UPPER(CONCAT(SUBSTRING(lusu.firstname,1,1), SUBSTRING(lusu.lastname,1,1)))) as forUser,
p.priority,s.status,hd.customerId,hd.locationId,hd.areaId,hd.assetId,hd.projectId,
m.createdAt as latestMessage, m.text as LastMessage
FROM listhdthreads as hd 
LEFT JOIN listhdmessages m ON hd.idx = m.parentThreadId 
LEFT JOIN listcustomers c ON hd.customerId = c.idx 
LEFT JOIN listlocations l ON hd.locationId = l.idx 
LEFT JOIN listprojects lp ON hd.projectId = lp.idx
LEFT JOIN listusers lu ON hd.contactId = lu.idx 
LEFT JOIN listusers lus ON hd.fromUser = lus.idx 
LEFT JOIN listusers lusu ON hd.respondingUser = lusu.idx
LEFT JOIN deschdtitles t ON hd.title = t.idx 
LEFT JOIN deschdpriorities p ON hd.priority = p.idx 
LEFT JOIN deschdstatus s ON hd.status = s.idx 
LEFT JOIN helpmessageparticipants hmp ON hd.idx = hmp.parentThreadId
ORDER BY hd.idx, m.createdAt DESC) as t
GROUP BY t.idx

Answer 1

您可以在 ON 子句中使用相关子查询。在子查询中，您可以从线程中获取最后一个(最大)消息 idx。

SELECT hd.*, m.* -- select columns you need
FROM listhdthreads as hd 
LEFT JOIN listhdmessages m ON m.idx = (
    SELECT MAX(mmax.idx)
    FROM listhdmessages mmax
    WHERE mmax.parentThreadId = hd.idx
)
-- JOIN more tables

使用 AUTO_INCRMENT 列比 createdAt 更好、更简单。 createdAt 不是唯一的，并且来自同一线程的两条消息可以在同一秒发布，这一事实就足够了。