mysql - 当特定列不为空时，按优先级获取最大值

Question

我有像这样的数据库表

transactions
-----------
id
code
date
amount


formalities
-----------
id
transaction_id

这是获取交易最大值的查询

SELECT MAX(transaction_id) FROM `transactions` n LEFT JOIN `formalities` r ON r.transaction_id = n.id

但是我在这里要实现的是通过交易代码获得id组的最大值，但该值必须检查交易是否与手续有关。

如果是，请在它们相关的地方获得最大值。如果不只是获得通常的最大值。是否有查询来实现这样的目标？

例子：

transactions
--------------------
id | code | amount |
1 | ABC | 10000 |
2 | ABC | 20000 |
3 | KOO | 10000 |
4 | ABC | 20000 |
5 | KOO | 30000 |
6 | KOO | 10000 |

formalities
-----------
id | transaction_id |
1 | 3 |
2 | 5 |

我想要的结果是从事务表中获得以下输出

id
-- 
4 
5 ( priority the one that have relation and get the max value)

标签： mysqldatabase

Answer 1

使用 LEFT JOIN 并同时获得 -MAX(transactions.id)和MAX(formalities.transaction_id)：

select t.code, max(f.transaction_id), max(t.id)
from transactions t
left join formalities f on f.transaction_id = t.id
group by t.code

结果将是

| code | max(f.transaction_id) | max(t.id) |
| ---- | --------------------- | --------- |
| ABC  | NULL                  | 4         |
| KOO  | 5                     | 6         |

在 DB Fiddle 上查看

要从表中“优先化”transaction_id列，formalities您可以使用COALESCE()，这将返回第一个非 NULL 值：

select t.code, coalesce(max(f.transaction_id), max(t.id)) as max_transaction_id
from transactions t
left join formalities f on f.transaction_id = t.id
group by t.code

结果：

| code | max_transaction_id |
| ---- | ------------------ |
| ABC  | 4                  |
| KOO  | 5                  |

在 DB Fiddle 上查看