通过函数获取详细级别组中列的最大值

Question

对于product_code,site_name,station_type和created_at的组合，我想选择具有最大distrongn的行。

这是我的代码。

Select a.* from insight_info a,
(select insight_info.id,product_code, site_name, station_type, 
created_at = (SELECT DATE(created_at) from insight_info),
max(dist_sn)
from insight_info 
group by product_code, site_name, station_type,insight_info.id,
created_at= (SELECT DATE(created_at) from insight_info)) b 
where a.product_code = b.product_code
and a.site_name = b.site_name
and a.station_type = b.station_type
and a.created_at = b.created_at
and a.product_code ='D00' 
and a.site_name = 'F00'
and a.station_type='A00';

这是我得到的错误。错误:列b.created_at不存在第10行:and a.created_at = b.created_at

如果不将created_at时间戳转换为日期，查询将不会出现错误。但仍然没有得到期望的输出。它收集了该组中所有的distrongn，而不仅仅是最大值。

Answer 1

因此，修复错误所需要做的就是在内部查询中添加别名。所以你的内部查询从

(SELECT DATE(created_at) from insight_info)) b 

(SELECT DATE(created_at) as created_at from insight_info)) b