MySQL在另一个表中按投票排序

Question

我有这样的表格:

entries:

ID      TITLE
1       Xyhargka
2       Hypothermia
3       Scale

和

votes:

ID      VOTE1       VOTE2   USERNAME
1       1           2       Entity
2       1           3       RobertG

VOTE1 和 VOTE2 具有条目 ID。 VOTE1 计 2 票，VOTE2 计 1 票。

我正在尝试构建一个返回以下内容的查询:

ID      TITLE           VOTES
1       Xyhargka        3
3       Scale           2
2       Hypothermia     1

我一直在查看 JOIN 语句，但我似乎无法弄清楚...我知道这不是有效的 MySQL，但这是我想要的伪代码:

SELECT v1 + v2 as votes, entries.* FROM entries
    JOIN (SELECT count(vote1) * 2 as v1 FROM votes WHERE vote1 = entries.id)
    JOIN (SELECT count(vote2) * 1 as v2 FROM votes WHERE vote2 = entries.id)
ORDER BY votes

Answer 1

这就是我在 SQLSERVER 中执行此操作的方式，不确定 MySQL 中是否存在差异

SELECT V.ID,
  SUM( V.COUNT) AS TOTALCOUNT
FROM  (SELECT E1.ID AS ID, 2 AS COUNT
FROM VOTES AS V
JOIN ENTRIES AS E1
  ON V.VOTE1 = E1.ID
UNION ALL
SELECT E2.ID AS ID, 1 AS COUNT
FROM VOTES AS V
JOIN ENTRIES AS E2
  ON V.VOTE2 = E2.ID) AS V
GROUP BY V.ID
ORDER BY TOTALCOUNT DESC