SQL:返回指定列max()行中的选择

Question

我希望返回在max()中使用SELECT的每一行的日期列。或者有更好的方法来做这件事？

我就是这样想的：

SELECT
    MAX(time) as time, [date column from max(time) row] as timedate,
    MAX(distance) as distance, [date column from max(distance) row] as distancedate,
    MAX(weight) as weight, [date column from max(weight) row] as weightdate

下面是我当前的SQL，它不返回MAX()行的日期。

$db->query("SELECT e.id as id, e.name, MAX(ue.time) as time, MAX(ue.weight) as weight, MAX(ue.distance) as distance
            FROM `users exercises` as ue
            LEFT JOIN `exercises` as e ON exerciseid = e.id
            GROUP BY e.id
            LIMIT 30");


id  | exerciseid | date       | weight | distance  | time
----------------------------------------------------------
1   | 1          | 2014-06-14 | 100    | 33        | null
2   | 1          | 2013-03-03 | 500    | 11        | null
3   | 1          | 2014-11-11 | null   | null      | 41

当前产出：

Array
(
    [id] => 1
    [name] => run
    [time] => 41
    [weight] => 500
    [distance] => 33
)

预期产出：

Array
(
    [id] => 1
    [name] => run
    [time] => 41
    [time_date] => 2014-11-11
    [weight] => 500
    [weight_date] => 2013-03-03 
    [distance] => 33
    [distance_date] => 2014-06-14
)

SQL：http://sqlfiddle.com/#!2/75e53/1

Answer 1

SELECT e.id as id, e.name,

    MAX(ue.time) as time,
    (
        select date
        from `users exercises`
        WHERE time = MAX(ue.time) AND ue.`userid` = $userid
        LIMIT 1
    ) as time_date,

    MAX(ue.weight) as weight,
    (
        select date
        from `users exercises`
        WHERE weight = MAX(ue.weight) AND ue.`userid` = $userid
        LIMIT 1
    ) as weight_date,

    MAX(ue.distance) as distance,
    (
        select date
        from `users exercises`
        WHERE distance = MAX(ue.distance) AND ue.`userid` = $userid
        LIMIT 1
    ) as distance_date

FROM `users exercises` as ue
LEFT JOIN `exercises` as e ON exerciseid = e.id
WHERE ue.`userid` = $userid
GROUP BY e.id
LIMIT 30

Answer 2

也许有一种更有效的方法可以做到这一点，但遗憾的是，我的MySQL技能并不那么好；然而，下面的代码可以满足您的需要：

解决方案1

select
  mx.time
, t.date as timedate
, mx.distance
, d.date as distancedate
, mx.weight
, w.date as weightdate
from
(
    SELECT
      MAX(`time`) as `time`
    , MAX(`distance`) as `distance`
    , MAX(`weight`) as `weight`
    from `users exercises`
) as mx
inner join `users exercises` as t on t.time = mx.time
inner join `users exercises` as d on d.distance = mx.distance
inner join `users exercises` as w on w.weight = mx.weight;

解决方案2

select
  mx.time
, (select date from `users exercises` as x where x.time = mx.time limit 1) as timedate
, mx.distance
, (select date from `users exercises` as y where y.distance = mx.distance limit 1) as distancedate
, mx.weight
, (select date from `users exercises` as z where z.weight = mx.weight limit 1) as weightdate
from
(
    SELECT
      MAX(`time`) as `time`
    , MAX(`distance`) as `distance`
    , MAX(`weight`) as `weight`
    from `users exercises`
) as mx;

对于任何使用支持partition by的数据库的人来说，有一种更好的实现方法；遗憾的是，MySQL目前不支持该功能。

SQL：http://sqlfiddle.com/#!2/75e53/13