通过函数获得细节级别的最大列
对于product_code，site_name，station_type和create_at的组合，我想选择使用最大dist_sn的行。
这是我的代码。
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行：和A.CREATED_AT = b.CREATED_AT ，
而无需将created_at时间戳转换为日期，查询即可在没有错误的情况下运行。但是仍然没有给出所需的输出。它拾取了所有DIST_SN，而不仅仅是该组中的最大值。
For a combination of product_code,site_name,station_type and created_at, I want to pick the row with the maximum dist_sn.
This is my code.
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';
This is the error I'm getting.
ERROR: column b.created_at does not exist
LINE 10: and a.created_at = b.created_at
Without converting the created_at timestamp to date, the query runs without errors. But still doesn't give the desired output. It picked up all the dist_sn instead of just the max in that group.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
因此，解决错误所需要做的就是将别名放在内部查询中。因此，您的内部查询
从
so all you need to do to fix your error is put an alias in the inner query. So your inner query changes from
to