sql - 从 tbl 中选择，其中 tbl.field 是最小值

Question

我有选择

SELECT e.*, b.f1, b.fee FROM eq e JOIN br b ON e.country = b.country
WHERE e.name= 'some' AND b.fee = 1

我需要重写选择替换 AND b.fee = 1 到 AND b.fee = min fee from b

我不能....我可以 AND b.fee = (SELECT MIN(fee)FROM br) 但这是个糟糕的解决方案

Answer 1

如果你只想要一行，那么只需使用limit:

SELECT e.*, b.f1, b.fee
FROM eq e JOIN
     br b
     ON e.country = b.country
WHERE e.name = 'some'
ORDER BY b.fee
LIMIT 1;

如果你想要所有的行都有最小空闲，那么这里有一个方法:

SELECT e.*, b.f1, b.fee
FROM eq e JOIN
     (SELECT b.*, MIN(fee) OVER () as minfee
      FROM br b
     ) b
     ON e.country = b.country
WHERE e.name = 'some' AND b.fee = minfee;