MySQL JOIN 与 SUM

Question

这个问题可能已经被问过很多次，因此，请原谅我重复，但我似乎找不到这样的东西，也无法设法构建与我想要实现的目标类似的东西。

例如，假设我有以下表结构:

//tasks
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |  P  |  None   |  AI   |
| user  | int(11)      | NO   |     |  None   |       |
| data  | varchar(200) | NO   |     |  None   |       |
+-------+--------------+------+-----+---------+-------+

//votes
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |  P  |         |  AI   |
| user  | int(11)      | NO   |     |         |       |
| item  | int(11)      | NO   |     |         |       |
| up    | tinyint(1)   | NO   |     | 0       |       |
| down  | tinyint(1)   | NO   |     | 0       |       |
+-------+--------------+------+-----+---------+-------+

具有以下数据:

//tasks
+----+------+------------+
| id | user |    data    |
+----+------+------------+
| 1  |  1   | something  |
| 2  |  2   | lorem ip   |
| 3  |  1   |   biggy    |
+----+------+------------+

//votes
+----+------+------+----+------+
| id | user | item | up | down |
+----+------+------+----+------+
| 1  |  8   |  1   | 1  |  0   |
| 2  |  4   |  1   | 1  |  0   |
| 3  |  2   |  1   | 0  |  1   |
| 4  |  2   |  2   | 1  |  0   |
| 5  |  1   |  2   | 1  |  0   |
+----+------+------+----+------+

我想做类似的事情:

   SELECT r.* FROM `tasks` WHERE `user` = '1' r
LEFT JOIN (SELECT SUM(t.up) AS up,
                  SUM(t.down) AS down
             FROM `votes` t
            WHERE t.item = r.id) r ON r.id = t.item

是的，到目前为止这是我的查询，但它不起作用，我不知道如何更正它。

基本上，我想:

1. 从 tasks 表中选择所有，其中 user 是“x”
2. 将 tasks(在步骤 1 中选择)的每一行与 up 的总和，down< 的总和 来自 votes 表，其中 item 等于来自 tasks
的 id

这应该产生类似的东西(即。user = 1):

+----+------+------------+----+------+
| id | user |    data    | up | down |
+----+------+------------+----+------+
| 1  |  1   | something  | 3  |  1   |
| 3  |  1   |   biggy    | 0  |  0   |
+----+------+------------+----+------+

好吧，我希望你们能理解并能帮助我解决这个问题。

提前致谢!

Answer 1

WHERE 子句需要在连接下面，别名应该使用 AS 声明。这是您查询的(语法上)更正版本:

   SELECT r.*
     FROM `tasks`
LEFT JOIN (
             SELECT SUM(t.up) AS up,
                    SUM(t.down) AS down
               FROM `votes` AS t
              WHERE t.item = r.id
          ) AS r
       ON r.id = t.item
    WHERE `user` = '1'

我会这样做(未经测试):

   SELECT `tasks`.`id`,
          `tasks`.`user`,
          `tasks`.`data`,
          `votes`.`up`,
          `votes`.`down`
     FROM `tasks`
LEFT JOIN (
               SELECT `item`,
                      SUM(`up`) AS `up`,
                      SUM(`down`) AS `down`
                 FROM `votes`
             GROUP BY `item`
          ) AS `votes`
       ON `votes`.`item` = `tasks`.`id`
    WHERE `tasks`.`user` = 1