时间戳

首页 > 解决方案 > 如何在一对多关系中选择每个匹配行的最小（最大）？

mysql - 如何在一对多关系中选择每个匹配行的最小（最大）？

问题描述

我有三张桌子，例如fruits：

+----+--------+---------+
| id | type   | variety |
+----+--------+---------+
|  1 | orange |     5   |
|  2 | orange |     7   |
|  3 | apple  |     1   |
|  4 | apple  |     0   |
+----+--------+---------+

containers：

+----+--------+
| id | year   | 
+----+--------+
|  1 | 2015   |
|  2 | 2020   |
|  3 | 2020   |
|  4 | 2018   |
+----+--------+

和inclusion：

+----+----------+---------+
| id | fruit_id | cont_id |
+----+----------+---------+
|  1 |      1   |     1   |
|  2 |      1   |     2   |
|  3 |      2   |     1   |
|  4 |      3   |     2   |
|  5 |      3   |     3   |
|  6 |      3   |     4   |
+----+----------+---------+

如果有的话，我需要为每个水果品种选择“最新”的容器：

+----+--------+----------+------+
| id | type   | variety  | year |
+----+--------+----------+------+
|  1 | orange |    5     | 2020 |
|  2 | orange |    7     | 2015 |
|  3 | apple  |    1     | 2020 |
|  4 | apple  |    0     | NULL |
+----+--------+----------+------+

我正在尝试类似的东西

SELECT * FROM `fruits`
LEFT JOIN (SELECT * FROM `containers` 
           JOIN `inclusion` ON `inclusion`.`cont_id` = `containers`.`id` 
           WHERE `fruit_id` = `fruits`.`id` 
           ORDER BY `year` DESC LIMIT 1
          ) `tops` ON `tops`.`fruit_id` = `fruits`.`id`;

但它说

ERROR 1054 (42S22): Unknown column 'fruits.id' in 'where clause'

有没有办法得到所需的结果？

我现在正在使用mariadb我的，但可能会迁移到mysql，所以我需要一个在两台服务器上都可以工作的解决方案。

如果我还添加cnt_type表怎么办：

+----+---------+
| id | type    |
+----+---------+
|  1 | box     |
|  2 | package |
+----+---------+

并将containers包括类型：

+----+--------+------+
| id | year   | type |
+----+--------+------+
|  1 | 2015   | 1    |
|  2 | 2020   | 1    |
|  3 | 2020   | 2    |
|  4 | 2018   | 2    |
+----+--------+------+

所以我需要提取每种容器类型的顶级年份，包括每种水果品种？

+----+--------+----------+----------+------+
| id | type   | variety  | cnt_type | year |
+----+--------+----------+----------+------+
|  1 | orange |    5     | box      | 2020 |
|  1 | orange |    5     | package  | NULL |
|  2 | orange |    7     | box      | 2015 |
|  2 | orange |    7     | package  | NULL |
|  3 | apple  |    1     | box      | 2020 |
|  3 | apple  |    1     | package  | 2020 |
|  4 | apple  |    0     | box      | NULL |
|  4 | apple  |    0     | package  | NULL |
+----+--------+----------+----------+------+

在这种情况下，组合type-year每个容器都应该是唯一的。

标签： mysqlsqlmariadb

解决方案

如果你最终坚持使用 MySQL 5.7（并且没有窗口函数），你可以使用这个查询（它也适用于 MariaDB）。该问题可以通过一个相当简单的选择来解决，该选择MAX(containers.year)按水果中的所有列和容器类型进行分组。需要注意 aCROSS JOIN以确保所有水果/容器组合都包含在输出中：fruitscnt_type

SELECT f.id, f.type, f.variety, 
       ct.type AS cnt_type,
       MAX(c.year) AS year
FROM fruits f
CROSS JOIN cnt_type ct
LEFT JOIN inclusion i ON i.fruit_id = f.id
LEFT JOIN containers c ON c.id = i.cont_id AND c.type = ct.id
GROUP BY f.id, f.type, f.variety, ct.type

输出：

id  type    variety     cnt_type    year
1   orange  5           box         2020
1   orange  5           package     null
2   orange  7           box         2015
2   orange  7           package     null
3   apple   1           box         2020
3   apple   1           package     2020
4   apple   0           box         null
4   apple   0           package     null

db-fiddle 上的演示

推荐阅读