从tbl.field为min的tbl中选择

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;