如何在SQL case语句中获取两个日期中的最新日期

Question

我有一个SQL表，需要检查两个日期中的最新日期，创建一个新列。这两个值可能同时存在，也可能不存在，在这种情况下，它默认为一个值。

我有一个这样的生产案例：

+----------+----------+-----------+
|  device  | activity |   date    |
+----------+----------+-----------+
| device 1 | stage    | 3/20/2018 |
| device 1 | test     | 3/30/2018 |
| device 2 | stage    | 6/1/2018  |
| device 2 | test     | [null]    |
+----------+----------+-----------+

我需要得到这个：

+----------+-----------------+
|  device  | stage_test_date |
+----------+-----------------+
| device 1 | 3/30/2018       |
| device 2 | 6/1/2018        |
+----------+-----------------+

我试着这样做：

case 
when activity in ('stage', 'test') 
then (select max(date))
else null
end as stage_test_date,

但我得到"不支持给定的相关子查询"错误。这是使用ANSI SQL的PrestoDB。它适用于"然后(选择日期("，但这并不能给我两个日期中最好的一个。

非常感谢你的建议！

Answer 1

为什么不直接这么做呢？

select device, max(date)
from t
group by device;

如果您想限制活动，也可以添加where：

select device, max(date)
from t
where activity in ('stage', 'test') 
group by device;

Answer 2

您可以使用相关的subquery方法：

select t.*
from table t
where activity in ('stage', 'test') and
date = (select max(t1.date) from table t1 where t1.device = t.device);

您也可以使用GROUP BY条款：

select device, max(date) as stage_test_date
from table t
where activity in ('stage', 'test')
group by device; 

Answer 3

在子查询中使用分析max((：

select
case 
when activity in ('stage', 'test') 
then max_date 
end as stage_test_date,
...
from
(
select t.*, 
max(date) over () as max_date 
from table t
) s
;

Answer 4

您可以使用此

select device, max(date) as stage_test_date 
from production 
group by device
order by device asc