mysql - 一列中的最大值和另一列中的最小值

Question

例如，如果 A 列和 B 列有值:

+---+---+
| A | B |
+---+---+
| 2 | 1 |
| 5 | 1 | 
| 6 | 1 |
| 1 | 2 |
| 5 | 2 |
| 0 | 2 |
| 2 | 3 |
| 7 | 3 |
| 4 | 3 |
| 5 | 4 |
+---+---+

从每组 B 中，我想从 A 中获得最大的数字。但是，我不想包括 B 中的数字更高但 A 值小于前一个的结果。我知道这在语言上没有意义，但这就是我希望最终结果看起来像的样子:

+---+---+
| A | B |
+---+---+
| 6 | 1 |
| 7 | 3 |
+---+---+

到目前为止，我有类似“select max(a), b from table1 group by b”的内容，但这并没有忽略 B 较高但最大 A 较小的那些。我知道我可以在 PHP 中细读该查询的结果并删除 A 值小于先前 A 值的结果，但我想尽可能将其全部放在 mysql 查询中。

Answer 1

此技术根据自身的聚合版本连接表，但连接偏移一个，因此每一行都连接到前一个 B 的 MAX(A) 值的知识。然后它匹配当前 A 大于其中任何一个的行，如果没有找到任何行，则不包括该行。然后，我们汇总最终选择以获得您想要的结果。

SELECT 
       MAX(source_row.A) as A, 
       source_row.B
  FROM ab as source_row
  LEFT JOIN (SELECT MAX(A) as A, B FROM ab GROUP BY B) AS one_back 
    ON one_back.B = source_row.B-1 
 WHERE (one_back.A IS NULL) 
    OR one_back.A < source_row.A
 GROUP BY B

我已经测试过了:-)

编辑:额外的见解

我想分享一些关于我是如何想出这些解决方案的见解；因为我认为人们开始“按集合思考”很重要……这是我读过的关于 JOINS 的最佳建议，您需要设想您的查询正在处理的中间“集合”。为了说明这一点，这里是中间“集合”的表示，它是该查询的关键部分；它是表格，因为它逐一“连接”到自身的聚合版本。

+------+------+------------+------------+
| A    | B    | one_back.B | one_back.A |
+------+------+------------+------------+
|    2 |    1 |       NULL |       NULL |
|    5 |    1 |       NULL |       NULL |
|    6 |    1 |       NULL |       NULL |
|    1 |    2 |          1 |          6 |
|    5 |    2 |          1 |          6 |
|    0 |    2 |          1 |          6 |
|    2 |    3 |          2 |          5 |
|    7 |    3 |          2 |          5 |
|    4 |    3 |          2 |          5 |
|    5 |    4 |          3 |          7 |
+------+------+------------+------------+

然后集合实际上是在内存中创建的(完整的连接版本永远不会完全在内存中，因为 MySQL 可以在知道行不会删除时立即删除行“切入”:

+------+------+------------+------------+
| A    | B    | one_back.B | one_back.A |
+------+------+------------+------------+
|    2 |    1 |       NULL |       NULL |
|    5 |    1 |       NULL |       NULL |
|    6 |    1 |       NULL |       NULL |
|    7 |    3 |          2 |          5 |
+------+------+------------+------------+

然后，当然，它将那里的结果聚合成最终形式，只从原始行中选择 A 和 B。