按评论数量对产品进行排序

问题描述：

I have a mysql database with

table 'product' containing columns 'product_id', 'name', 'price'
table 'review' containing columns 'review_id', 'product_id', 'review_content'

I want to retrieve results for 'product_id' = 123, but sort if by number of reviews. I am able to find the number of reviews for a particular product using COUNT('review_id'), but how do I sort the results by number of reviews?

我有一个带有 p>

table'的mysql数据库产品'包含列'product_id'，'name'，'price' li>
表'review'包含列'review_id'，'product_id'，'review_content' li> ul >
我想检索'product_id'= 123的结果，但按评论数量排序。我可以使用COUNT（'review_id'）查找特定产品的评论数量，但如何按评论数量对结果进行排序？ p> div>

答

Since you're presumably selecting COUNT('review_id') in your query, you can simply add ORDER BY COUNT('review_id') DESC at the end.

答

Try this to get all the products and the number of reviews on each:

SELECT P.*, IFNULL(COUNT(R.product_ID),0) AS NumReviews
FROM Product AS P
LEFT JOIN Review AS R ON R.product_id = p.product_id
ORDER BY  COUNT(R.product_ID) DESC

答

To save doing the count twice use: count('review_id') as num_reviews and then order by num_reviews

答

If you want the products with the most reviews first...

SELECT P.product_id, 
       IFNULL(COUNT(R.product_ID),0) as "Reviews"
FROM product as P
LEFT JOIN review as R ON P.product_ID = R.product_id
GROUP BY P.product_id
ORDER BY Review DESC;

...otherwise switch DESC with ASC.

按评论数量对产品进行排序

相关推荐