我有3个表,它们的列名如下:
餐馆:
- id,名称,烹饪,地址,image_file
评论:
- id,restaurant_id(外键(,user_id(主键(,header,content,posted_at,modified_at
用户:-id,用户名,电子邮件,密码,权限
在我的PHP项目主页上,如果有评论,我想显示所有有最新评论的餐厅。有人能帮助我如何在单个查询和单个foreach循环中做到这一点吗?我尝试过使用子查询进行左联接,但无法解决重复行的问题。提前感谢!
试试这个
SELECT *
FROM restaurants
LEFT JOIN
(
SELECT restaurant_id AS restId, MAX(posted_at) AS lastTime
FROM reviews
GROUP BY restaurant_id
) AS last_reviews
ON last_reviews.restId = restaurants.id
LEFT JOIN reviews
ON reviews.posted_at = last_reviews.lastTime
AND reviews.restaurant_id = restaurants.id