mysql sum代码_带SUM的MySQL连接

这个问题可能已经被问了很多次了,所以,请原谅我重复了一遍,但是我似乎找不到这样的东西,我也无法建立类似于我想要实现的东西。

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

//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

是的,到目前为止这是我的问题,但它不起作用,我不知道如何纠正。

基本上,我想:

选择

一切

从表

任务

哪里

user

是“x”

连接中的每一行

任务

(在步骤1中选择)使用

总和

up

,

总和

down

从表

投票

哪里

item

等于

id

从

任务

这应该会产生类似的结果。

用户

=1):

+----+------+------------+----+------+

| id | user | data | up | down |

+----+------+------------+----+------+

| 1 | 1 | something | 3 | 1 |

| 3 | 1 | biggy | 0 | 0 |

+----+------+------------+----+------+

好吧,我希望你们能理解并帮助我。

提前谢谢!