我想这就是你要找的。尝试并确认。：）

SELECT 
      res.ResName
      ,item.Name as ResItem
      ,(Select max(SubItem.rating) from RESTAURANT_ITEMS SubItem where res.RestaurantID= SubItem.RestaurantID) as Rating
FROM 
      RESTAURANTS res
LEFT JOIN 
      RESTAURANT_ITEMS item on res.RestaurantID = item.RestaurantID
WHERE 
      res.Restaurant_ID in (1,2,3,4,5)

问题更新后，编辑了以下部分

SELECT 
      res.Name as [Restaurant Name]
      ,item.Name as [Top Item]
FROM 
      RESTAURANTS res
LEFT JOIN 
      RESTAURANT_ITEMS item on res.RestaurantID = item.RestaurantID 
                            and rating = (select max(subItems.rating) from RESTAURANT_ITEMS subItems where subItems.RestaurantID = res.RestaurantID)
WHERE 
      res.Restaurant_ID in (1,2,3,4,5)

希望这对你有帮助：）

您可以在派生表中获得餐厅的最高评级值。
将它连接回主表以获得所需的输出
请尝试以下操作：

SELECT r.id, 
       r.name, 
       ri.name AS top_item 
FROM RESTAURANTS AS r 
LEFT JOIN (SELECT FK_RES_ID, MAX(rating) AS max_rating 
           FROM RESTAURANT_ITEMS 
           GROUP BY FK_RES_ID) AS dt 
  ON dt.FK_RES_ID = r.id 
LEFT JOIN RESTAURANT_ITEMS AS ri ON ri.FK_RES_ID = dt.FK_RES_ID AND 
                                    ri.rating = dt.max_rating 

WHERE r.id IN (1,2,3,4)