MySQL SELECT行的ID，其中几列的MAX条目最大 [英] MySQL SELECT id of row where GREATEST of MAX entries of several columns

问题描述

我有这张桌子mytable:

I have this table 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，结果如下:

Now I try to have one SELECT with the following result:

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

这意味着每个日期列的最大值，以及MAX的最大结果所在行中的ID.

That means the MAX of each date column and the ID from the row in which the GREATEST of the MAX results is.

将查询带到解决方案的查询看起来像这样:

The Query, that brought me nearly to the solution looks like this:

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?

But the id i get, is not the expected one. How can I find out which column has the maxdate so I can find out the appendant id?

请参阅小提琴.

推荐答案

一种方法是将日期存储在用户定义的变量中.那么您可以使用它来提取最大日期的ID

a way to do it would be to store your date in a user-defined-variable. then you can use it to pull out the id for the largest date

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

游戏演示

这篇关于MySQL SELECT行的ID，其中几列的MAX条目最大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！