gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-30 22:55:10 27 4
gpt4 key购买 nike

我有这张表 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 .

最佳答案

一种方法是将您的日期存储在用户定义的变量中。然后你可以用它来提取最大日期的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

关于MySQL SELECT id of row where GREATEST of MAX entries of 几列,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26738338/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com