Since you're presumably selecting
COUNT('review_id') in your query, you can simply add
ORDER BY COUNT('review_id') DESC at the end.
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?
Since you're presumably selecting点赞 评论 复制链接分享
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;
ASC.点赞 评论 复制链接分享
To save doing the count twice use:
count('review_id') as num_reviewsand then
order by num_reviews点赞 评论 复制链接分享
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点赞 评论 复制链接分享