MySQL - 如何对 "nested"数据集使用 GROUP BY/ORDER BY？

Question

我的(子)查询结果在以下数据集中:

+---------+------------+-----------+
| item_id | version_id | relevance |
+---------+------------+-----------+
|       1 |          1 |        30 |
|       1 |          2 |        30 |
|       2 |          3 |        22 |
|       3 |          4 |        30 |
|       4 |          5 |        18 |
|       3 |          6 |        30 |
|       2 |          7 |        22 |
|       1 |          8 |        30 |
|       5 |          9 |        48 |
|       4 |         10 |        18 |
|       5 |         11 |        48 |
|       3 |         12 |        30 |
|       3 |         13 |        31 |
|       4 |         14 |        19 |
|       2 |         15 |        22 |
|       1 |         16 |        30 |
|       5 |         17 |        49 |
|       2 |         18 |        22 |
+---------+------------+-----------+
18 rows in set (0.00 sec)

项目和版本存储在单独的 InnoDB 表中。两个表都有自动递增的主键。版本具有项目的外键 (item_id)。

我的问题:如何根据相关性获取子集？

我想获取以下包含最相关版本的子集:

+---------+------------+-----------+
| item_id | version_id | relevance |
+---------+------------+-----------+
|       1 |         16 |        30 |
|       2 |         18 |        22 |
|       3 |         13 |        31 |
|       4 |         14 |        19 |
|       5 |         17 |        49 |
+---------+------------+-----------+

在同等相关性的情况下，获取 MAX(version_id) 会更理想。

我尝试了多种方式的分组、加入、排序等，但我无法获得想要的结果。我尝试过的一些事情是:

SELECT    item_id, version_id, relevance
FROM      (subquery) a
GROUP BY  item_id
ORDER BY  relevance DESC, version_id DESC

当然，排序发生在事后，因此相关性和 MAX(version_id) 信息都丢失了。

请指教。

Answer 1

这是你可以这样做的方法:

SELECT t1.item_id, max(t1.version_id), t1.relevance FROM t t1
LEFT JOIN t t2 ON t1.item_id = t2.item_id AND t1.relevance < t2.relevance
WHERE t2.relevance IS NULL
GROUP BY t1.item_id
ORDER BY t1.item_id, t1.version_id

输出:

| ITEM_ID | VERSION_ID | RELEVANCE |
|---------|------------|-----------|
|       1 |         16 |        30 |
|       2 |         18 |        22 |
|       3 |         13 |        31 |
|       4 |         14 |        19 |
|       5 |         17 |        49 |

fiddle here .