douwo8140
2015-07-28 19:47 阅读 32
已采纳

如何将recipe_id不同的所有评级列值相加

To be more specific I have a table with recipes. In another table I keep ratings associated with these recipes. Each rating has an unique id, the second column is the recipe's id, the third column is the rating (from 1-5).

Expected result is the recipe_id, where all ratings associated with that recipe_id are the highest.

I hope you understand, I have no idea how to approach such query properly.

Hopefully I've posted my question properly, it's my first one.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    duanhe4267 duanhe4267 2015-07-28 19:52

    You could use a self-join:

    SELECT
      r.id,
      r.recipe_id,
      r.rating
    FROM
      recipes_ratings r LEFT JOIN recipes_ratings r2
      ON r.recipe_id = r2.recipe_id
         AND r2.rating>r.rating
    WHERE
      r2.rating IS NULL
    

    Please see a fiddle here.

    点赞 评论 复制链接分享

相关推荐