MySQL 选择所有具有最小值的子查询行

Question

我想选择子查询中给定字段中具有最小值的所有行。以下是我迄今为止尝试过的技术的一些简单示例:

-- 1.
select
    id, min(foo)
from
    (select 1 AS id, 2 AS foo
    union select 2 AS id, 2 AS foo
    union select 3 AS id, 3 AS foo) a;

-- 2.
select
    min(foo)
from
    (     
          select 1 AS id, 2 AS foo, 0 AS const
    union select 2 AS id, 2 AS foo, 0 AS const
    union select 3 AS id, 3 AS foo, 0 AS const) a
group by const;

-- 3.
select
    id
from
    (select 1 AS id, 2 AS foo
    union select 2 AS id, 2 AS foo
    union select 3 AS id, 3 AS foo) a
where id = (select id from a where min(foo) = foo);

-- 4.
select
    id
from
    (select 1 AS id, 2 AS foo
    union select 2 AS id, 2 AS foo
    union select 3 AS id, 3 AS foo) a
where foo = (select min(foo));

-- 5.
select r.*
from
(
    select min(foo) t
    from
        (select 1 AS id, 2 AS foo
        union select 2 AS id, 2 AS foo
        union select 3 AS id, 3 AS foo) a
) m
INNER JOIN a ON m.t = r.foo;

我正在处理的实际查询与示例类似，因为它由多个较小的查询联合在一起组成。这里的总体目标是根据其所连接的关联表k的字段在中央表中查找行，其中k> 是最高优先级表。结果是类似(但不同的表)中的行的 TreeView 。

我提到这一点是为了万一有人看到我正在以一种迂回的方式解决这个问题，他们可以从更大的角度来阐明这一点。但现在我的角度是通过在子查询中的字段上取最小值来选择。

Answer 1

使用order by和limit:

select t.*
from t
order by foo
limit 1;

注意:即使存在重复项，这也仅返回具有最小值的一行。 t 是您的子查询或表。

如果您想要所有这些，那么您需要包含表定义两次:

select t.*
from t
where t.foo = (select min(t2.foo) from t t2);