mysql - 获取 x 列的 MAX，同时获取 y 列的 DISTINCT

Question

我想加入这些表(task_id 和 post_id)，这将返回您在下面看到的所有内容。这很好，但我接下来要做的是删除 task_id 的重复项，同时保留具有最高版本的项目。

我有 2 个表，如下所示:

项目

ID    title    task_id    project_id     version
1    "Test"      123         456            1
2   "Test 2"     124         456            1
3   "Test 3"     125         456            1
4    "X 3.1"     125         456           1.1
5    "X 3.2"     125         456           1.2

任务

ID    post_id    meta_key    meta_value
1       123    _completed       0
4       124    _completed       0
5       125    _completed       0

到目前为止我有这个 SQL 语句:

SELECT * 
FROM items t0 
INNER JOIN tasks AS t1 
 ON t0.task_id = t1.post_id 
WHERE t1.meta_key = '_completed' 
 AND project_id = 456

返回:

ID    title    task_id    project_id    version   ID    post_id    meta_key    meta_value
1    "Test"      123         456           1      1       123    _completed       0
2   "Test 2"     124         456           1      1       124    _completed       0
3   "Test 3"     125         456           1      1       125    _completed       0
4    "X 3.1"     125         456          1.1     1       125    _completed       0
5    "X 3.2"     125         456          1.2     1       125    _completed       0

如何删除重复的 task_id，但保留最高版本，以便表格为:

ID    title    task_id    project_id    version   ID    post_id    meta_key    meta_value
1    "Test"      123         456           1      1       123    _completed       0
2   "Test 2"     124         456           1      1       124    _completed       0
5    "X 3.2"     125         456          1.2     1       125    _completed       0

基本上我最接近的是按版本排序并使用 GROUP BY task_id 但显然 SQL 不允许你这样做。

有人能想出一个实现这一目标的好方法吗？

Answer 1

根据相关帖子，我认为这应该可行。

SELECT *
FROM items t0
INNER JOIN tasks AS t1 ON t0.task_id = t1.post_id
INNER JOIN
    (SELECT task_id, MAX(version) AS MaxVersion
    FROM items
    GROUP BY task_id) groupedt0 
ON t0.task_id = groupedt0.task_id 
AND t0.version = groupedt0.MaxVersion
AND project_id = 456
AND t1.meta_key = '_completed'
ORDER BY ID ASC

参见:How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?