jquery - 我的 SQL 查询显示用户评论中最有用的评论

Question

我想经营一个电子商务网站并销售产品。我想获得每个用户的评分和评论以提高质量。所以在一个单独的页面上，我想获得用户的评分和评论。因此其他用户可以将评论标记为有用或无用。因此，无论何时用户选择发布的评论是否有用，我都会在新表中创建一个数据库条目。所以我的要求是，在新页面上，我想根据用户选择的最有帮助的评论列出评论。

所以我想首先显示基于大多数有用评论的结果(按降序排列)

我的表结构如下。rating_review 表: rating_review_helpful 表

我已经在下面粘贴了我的 sql 查询。

SELECT *,
       rating_reviews_helpful.rating_id,
       COUNT(*) AS COUNT
FROM `rating_reviews_helpful`
JOIN rating_reviews ON rating_reviews.shop_id = rating_reviews_helpful.deal_id
JOIN users ON users.user_id=rating_reviews.user_id
WHERE rating_reviews_helpful.review_helpful =1
  AND rating_reviews.rating_status = 1
  AND rating_reviews_helpful.deal_id = 12
  AND rating_reviews_helpful.type = 2
GROUP BY rating_reviews_helpful.rating_id
ORDER BY COUNT DESC

上述查询的结果如下。

我坚持了很长时间。任何人都可以帮助我摆脱困境吗？提前致谢!

Answer 1

You can try the following query to get the result

  SELECT (add all the fields you require) ,B.rating_id
FROM    `rating_reviews_helpful` rrh1
JOIN rating_reviews rr1 ON rr1.shop_id = rrh1.deal_id
JOIN users u1 ON u1.user_id=rr1.user_id JOIN (SELECT rrh.rating_id rating_id,
       COUNT(*) AS COUNT
FROM `rating_reviews_helpful` rrh
JOIN rating_reviews rr ON rr.shop_id = rrh.deal_id
JOIN users u ON u.user_id=rr.user_id
WHERE rrh.review_helpful =1
  AND rr.rating_status = 1
  AND rrh.deal_id = 12
  AND rrh.type = 2
GROUP BY rrh.rating_id) B ON rrh1.rating_id = B.rating_id
WHERE rrh1.review_helpful =1
  AND rrh1 = 1
  AND rrh1.deal_id = 12
  AND rrh1.type = 2
ORDER BY B.COUNT DESC