我有以下数据,需要查看操作等于‘已用’的最新日期:
CAT STAMP ACTION
A 05/12/2019 00:13 USED
A 05/12/2019 07:56 USED
A 05/12/2019 09:05 NEW
A 05/12/2019 10:46 NEW
B 20/12/2019 20:50 USED
B 13/01/2020 14:50 USED
B 10/01/2020 22:39 NEW
B 05/12/2019 12:04 NEW对于上面的代码,我需要它返回:
A 05/12/2019 07:56 USED
B 13/01/2020 14:50 USED对于每个“猫”,每天可能有数百个条目,它需要回顾几年,只返回最近的条目。我已经尝试过MAX函数,但由于日期格式的原因,我认为它不起作用。
发布于 2020-01-24 23:44:20
您可以使用子查询:
select t.*
from table t
where t.action = 'used' and
t.stamp = (select max(t2.stamp) from table t1 where t1.stamp = t.stamp)发布于 2020-01-24 23:43:53
一种方法是使用相关子查询进行过滤:
select t.*
from mytable t
where
t.action = 'USED'
and stamp = (
select max(t1.stamp)
from mtable t1
where t1.cat = t.cat and t1.action = t.action
)另一个典型的解决方案是反left join模式:
select t.*
from mytable t
left join mytable t1
on t1.cat = t.cat
and t1.action = t.action
and t1.stamp > t.stamp
where t.action = 'USED' and t1.cat is null这两种解决方案都假定stamp以date-like数据类型存储。如果不是,则需要额外的步骤将字符串转换为日期。
发布于 2020-01-24 23:45:44
听起来像是stamp被存储为varchar而不是datetime。先试试CAST。
SELECT cat, MAX(CAST stamp AS DateTime) as MxDate, 'used' as Action
FROM mytable
WHERE Action = 'used'
GROUP BY cathttps://stackoverflow.com/questions/59899623
复制相似问题