MySQL SELECT id of row where GREATEST of MAX entries of 几列

Question

我有这张表 mytable:

+----+--------------------------------------+
| id | date1      | date2      | date3      |
+----+--------------------------------------+
| 1  | 2014-01-08 | NULL       | NULL       | 
| 2  | 2014-05-09 | NULL       | NULL       | 
| 3  | 2014-06-13 | NULL       | NULL       | 
| 4  | NULL       | 2014-03-24 | NULL       | 
| 2  | NULL       | NULL       | 2014-08-15 | 
| 4  | 2014-01-01 | NULL       | NULL       | 
| 1  | 2014-02-15 | NULL       | NULL       | 
| 3  | NULL       | 2014-12-06 | 2014-10-12 | 
| 4  | 2014-08-06 | NULL       | NULL       | 
| 2  | 2014-05-22 | NULL       | NULL       | 
+----+--------------------------------------+

现在我尝试使用一个具有以下结果的 SELECT:

  id   max_date1    max_date2    max_date3
---------------------------------------------
| 3  | 2014-08-06 | 2014-12-06 | 2014-10-12 | 

这意味着每个日期列的 MAX 和 MAX 结果中最大结果所在行的 ID。

几乎使我找到解决方案的查询如下所示:

SELECT
   id, max(date1), max(date2), max(date3), GREATEST(
     IFNULL(max(date1), 0),
     IFNULL(max(date2), 0),
     IFNULL(max(date3), 0)) AS maxdate
FROM table1

但我得到的 ID 不是预期的 ID。如何找出哪一列具有最大日期，以便找出附加 ID？

see fiddle .

Answer 1

一种方法是将您的日期存储在用户定义的变量中。然后你可以用它来提取最大日期的id

SET @A := (SELECT GREATEST(
                     IFNULL(max(date1), 0),
                     IFNULL(max(date2), 0),
                     IFNULL(max(date3), 0)
                  ) FROM table1
           );
-- here i JOIN a select that pulls out the correct id
SELECT t.joinid, max(`date1`), max(`date2`), max(`date3`)
FROM table1
JOIN 
(   SELECT id as joinid 
    FROM table1
    WHERE @A IN -- WHERE my MAX date is in
    (
        SELECT date1 -- here the UNION is just putting all of the dates into one column to compare one date with
        UNION ALL SELECT date2
        UNION ALL SELECT date3
    )
) t -- every table must have an alias

FIDDLE DEMO