使用这个SO答案,我创建了以下查询:
select
created, property_id, requesting_user_id, type, response
from
pdr t1
where
t1.created = (
select max(created)
from pdr t2
where t2.property_id = t1.property_id and t2.requesting_user_id = t1.requesting_user_id
)
这就像一个符咒,但现在我想使用联接将其(也正如我上面链接的SO答案中所建议的那样)转换为查询。所以我想出了这个:
select
created, property_id, requesting_user_id, type, response
from
pdr t1
inner join (
select max(created) as created, property_id, requesting_user_id
from pdr
group by property_id, requesting_user_id
) as t2 on t2.property_id = t1.property_id and t2.requesting_user_id = t1.requesting_user_id and t2.created = t1.created
不幸的是,这会返回错误信息ambiguous column name: created
。所以我把一些创建的东西放到前面t1.
或后面弄乱t2.
了,但是后来我遇到了各种各样的语法错误,所以我有点在这里迷路了。
有人可以帮我解决我在这里做错的事情吗?欢迎所有提示!
ps:我目前正在SQLite上对此进行测试,但最后它也应在MySQL上运行。如果有区别,当然也很有趣。
首先选择应该像:
SELECT t1.created, t1.property_id, t1.requesting_user_id, type, response...
您所做的所有其他操作都正确无误...
基于https://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html ...
GL
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句