MySQL查询以获取多个表上一列的第一个唯一值

Question

我有以下 SQL 查询查询我的票、ticketThreads、用户和 threadStatus 表:

SELECT tickets.threadId, ticketThreads.threadSubject, tickets.ticketCreatedDate,  ticketThreads.threadCreatedDate, threadStatus.threadStatus, users.name
FROM 
tickets 
INNER JOIN 
ticketThreads
ON
tickets.threadId = ticketThreads.threadId
INNER JOIN
threadStatus 
ON
ticketThreads.threadStatus = threadStatus.id
INNER JOIN
users
ON
users.id = ticketThreads.threadUserId
WHERE
tickets.ticketId = ticketThreads.lastMessage
AND 
ticketThreads.threadStatus != 3
ORDER BY
tickets.ticketCreatedDate
DESC

返回的缩略版是:

threadId | 
----------
1        |
2        |

这工作正常，并且是我所期望的，但是为了稍微清理代码和数据库，我需要删除 ticketThreads.lastMessage 列。

如果我删除 WHERE tickets.ticketId = ticketThreads.lastMessage 这行，那么这是返回内容的简化版本:

threadId | 
----------
1        |
2        |
1        |

然后我需要做的是编辑上面的查询，使我能够为票据数据库中的每个 threadId 值选择最大的唯一值。

我知道 MAX() 和 GROUP BY 但不知道如何将它们放入我上面的查询中。

表格的相关部分如下所示:

门票

ticketId | ticketUserId | threadId 
-------------------------------
1        | 1            | 1
2        | 1            | 2
3        | 1            | 1

ticketThreads

threadId | lastMessage  | threadStatus
-------------------------------
1        | 3            | 4
2        | 2            | 1

我希望以上所有内容都清楚且有意义

Answer 1

所以您需要每个线程具有最高 ID 的 ticket？您的问题实际上是 greatest record per group problem 的非常简单的变体.不需要任何子查询。基本上你有两个选项，两者都应该比你的查询执行得更好，第二个更快(请在你的数据库中发布实际持续时间!):

1。符合标准的查询，但速度较慢:

SELECT t1.threadId, ticketThreads.threadSubject, t1.ticketCreatedDate,  
    ticketThreads.threadCreatedDate, threadStatus.threadStatus, users.name
FROM tickets as t1
LEFT JOIN tickets as t2 
    ON t1.threadId = t2.threadId AND t1.ticketId < t2.ticketId
JOIN ticketThreads ON t1.threadId = ticketThreads.threadId
JOIN threadStatus ON ticketThreads.threadStatus = threadStatus.id
JOIN users ON users.id = ticketThreads.threadUserId
WHERE t2.threadId is NULL
    AND ticketThreads.threadStatus != 3
ORDER BY t1.ticketCreatedDate DESC

这个加入了 tickets 表两次，这对于大表来说可能会慢一些。

2。更快，但使用标准 SQL 的 MySQL 扩展:

set @prev_thread := NULL;

SELECT t.threadId, ticketThreads.threadSubject, t.ticketCreatedDate,  
    ticketThreads.threadCreatedDate, threadStatus.threadStatus, users.name
FROM tickets as t
JOIN ticketThreads ON t.threadId = ticketThreads.threadId
JOIN threadStatus ON ticketThreads.threadStatus = threadStatus.id
JOIN users ON users.id = ticketThreads.threadUserId
WHERE ticketThreads.threadStatus != 3
    AND IF(IFNULL(@prev_thread, -1) = @prev_thread := t.threadId, 0, 1)
ORDER BY t.threadId, t.ticketId DESC,
    t.ticketCreatedDate DESC

在这里，我们对有序连接数据执行一次扫描，使用辅助 mysql 变量 @prev_thread 为每个线程(具有最高 ticketId 的线程)过滤第一个(给定顺序)票证.