mysql受欢迎吗_MYSQL按喜欢/不喜欢和受欢迎程度排序

I have table of comments which includes likes and dislikes, now i have problem with proper order..

Actually my system shows comments with greatest amount of likes on top.

I'm looking for something like system in youtube.

It means that comment with 100like/100dislikes is higher in order than 1/1..

I hope this is understandable :)

解决方案

This is classic problem how to rank upvote/downvote, plus/minus, like/dislike and so on. There are a few possible solutions but they may give wrong result in specific conditions.

I strongly recommend reading and using ordering like in

PROBLEM:

You need some sort of "score" to sort by.

WRONG SOLUTION #1: Score = (Positive ratings) - (Negative ratings)

WRONG SOLUTION #2: Score = Average rating = (Positive ratings) /

(Total ratings)

CORRECT SOLUTION: Score = Lower bound of Wilson score confidence

interval for a Bernoulli parameter

Sample code (you can easily adapt it for your needs):

SELECT id, ((positive + 1.9208) / (positive + negative) -

1.96 * SQRT((positive * negative) / (positive + negative) + 0.9604) /

(positive + negative)) / (1 + 3.8416 / (positive + negative))

AS ci_lower_bound

FROM your_tab

WHERE positive + negative > 0

ORDER BY ci_lower_bound DESC;