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是最高优先级表。结果是来自类似（但不同的表）的行的一种树形视图。

我已经提到过这个，所以如果有人能看到我以迂回的方式讨论这个问题，他们可以在更大的图景上阐明一些事情。但现在我的角度是通过在子查询中的字段取最小值来选择。

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);