是否可以为一条记录选择所有评论,并根据另一个表中正面投票与负面投票的总体比例进行排序?
我想我可能需要使用subselect,但我真的不确定如何使用或在哪里使用。
2张表:
表1【注释】
ID | RecordID | Comment
------------------------------
1 | 100001 | blah blah
2 | 100202 | another co
3 | 100054 | lorem ips
表2【评级】
ID | CommentID | Vote
-------------------------
1 | 1 | 1
2 | 1 | 0
3 | 1 | 1
4 | 3 | 0
5 | 3 | 0
6 | 3 | 0
请注意:"投票":0=反对票;1=赞成票此外,我正在使用Microsoft SQL Server
感谢您的帮助:)
要将它们从正面评级到负面评级进行排序,可以执行以下操作:
SELECT c.id,
c.recordID,
c.comment
FROM comments c
INNER JOIN (
SELECT CommentID,
SUM(CASE WHEN Vote = 0 THEN -1 ELSE 1 END) AS RATING FROM ratings
GROUP BY commentID
) r ON r.commentID = c.id
ORDER BY r.RATING DESC;
sqlfiddle演示
请注意,我使用CASE来给0的投票值-1,否则,10个否定和1个肯定将给出1的评级。
此查询不考虑没有分数的评论。如果你想让没有分数的评论有0分,你可以做:
SELECT c.id,
c.recordID,
c.comment
FROM comments c
LEFT JOIN (
SELECT CommentID,
SUM(CASE WHEN Vote = 0 THEN -1 ELSE 1 END) AS RATING FROM ratings
GROUP BY commentID
) r ON r.commentID = c.id
ORDER BY COALESCE(r.RATING,0) DESC;
编辑:
要获得比率,请尝试以下操作:
SELECT c.id,
c.recordID,
c.comment,
r.rating
FROM comments c
LEFT JOIN (
SELECT CommentID,
SUM(CASE
WHEN Vote = 1
THEN 1
ELSE 0
END) / (nullif(COUNT(*) * 1.0, 0)) AS RATING
FROM ratings
GROUP BY commentID
) r ON r.commentID = c.id
ORDER BY COALESCE(r.RATING, 0) DESC;
sqlfiddle演示