我有一个问题。
我有两个表格,一个包括评论,另一个包括这些评论的投票。
my comments table:
--------------------
comment_id | comment
1 abc1
2 abc2
3 abc3
4 abc4
--------------------
my voting table:
------------------
user_id comment_id | voted
1 1 1 // comment 1 has the result +1 now
2 1 1 // comment 1 has the result +2 now
3 1 2 // comment 1 has the result +1 now
4 4 1 // comment 4 has the result +1 now
5 4 2 // comment 4 has the result 0 now
------------------
嗯,如果一个人喜欢的评论,它被保存为"1"到"投票"。如果一个人不喜欢的评论,它将被保存为"2"到"投票"。
$likes = $db->query('SELECT * FROM voting WHERE voted=1')->num_rows;
$dislikes = $db->query('SELECT * FROM voting WHERE voted=2')->num_rows;
$the_result = $likes-$dislikes;
例如,当5个人喜欢同一条评论,2个人不喜欢同一条评论时,我显示的结果是"+3"。
我想用最大的结果对它们排序。
如:第一个要显示的是+4,第二个要显示的是+2,第三个要显示的是-2。
我想在PHP中这样做。谢谢你的帮助。
很抱歉我解释得不好,这是我的第一个问题。(
我会尝试如下:
SELECT *,
(count(CASE WHEN vote = 1 then 1 ELSE NULL END) - (count(CASE WHEN vote = 2 then 1 ELSE NULL END))) as RESULT
FROM comments AS c
LEFT JOIN votes as v ON c.comment_id=v.comment_id
GROUP BY c.id
ORDER BY RESULT desc
计票:
只返回comment_id和总票数:检查代码
SELECT comment_id, SUM(
CASE voted
WHEN 1 THEN 1
ELSE -1
END) AS total
FROM voting
GROUP BY comment_id
ORDER BY total DESC;
返回带有投票的注释:检查代码
SELECT comment_id, comments, SUM(
CASE voted
WHEN 1 THEN 1
ELSE -1
END) AS total
FROM voting
INNER JOIN comments
USING(comment_id)
GROUP BY comment_id
ORDER BY total DESC;
返回所有行:检查代码
SELECT comment_id, comments, SUM(
CASE voted
WHEN 1 THEN 1
ELSE -1
END) AS total
FROM voting
RIGHT JOIN comments
USING(comment_id)
GROUP BY comment_id
ORDER BY total DESC;