[编辑：]我添加了值(1052,10,3,1290839091,1)以更好地解决问题,并且必须将“item_id”添加到提取最新投票的子查询中.
好极了！最后一个stackoverflow问题,我实际上可以回答！我已经浏览了一个星期寻找短暂的东西;甜蜜的我的胡同.感谢有趣的SQL问题！
首先,你需要提取最新的选票. (voter_id,item_id,vote_date)的组合更适合这种工作方式！
SELECT
voter_id, item_id, MAX(vote_date) AS vote_date
FROM
votes
GROUP BY
voter_id, item_id
结果：
+----------+---------+------------+
| voter_id | item_id | vote_date |
+----------+---------+------------+
| 3 | 9 | 1275021495 |
| 3 | 10 | 1290839091 |
| 27 | 9 | 1273770151 |
| 45 | 9 | 1274896188 |
| 181 | 9 | 1290839090 |
+----------+---------+------------+
然后你需要加入原始表格来反对这些结果.子选择将完成这项工作.请注意上面的查询是如何复制&粘贴到下面的连接中,但现在它的名称为“tmp”：
SELECT
v.*
FROM (
SELECT
voter_id, item_id, MAX(vote_date) AS vote_date
FROM
votes
GROUP BY
voter_id, item_id
) tmp
INNER JOIN
votes v ON ( v.vote_date = tmp.vote_date
AND v.voter_id = tmp.voter_id
AND v.item_id = tmp.item_id
)
结果：
+---------+---------+----------+------------+------+
| vote_id | item_id | voter_id | vote_date | vote |
+---------+---------+----------+------------+------+
| 60 | 9 | 27 | 1273770151 | 1 |
| 153 | 9 | 45 | 1274896188 | 1 |
| 164 | 9 | 3 | 1275021495 | 2 |
| 1051 | 9 | 181 | 1290839090 | 1 |
| 1052 | 10 | 3 | 1290839091 | 1 |
+---------+---------+----------+------------+------+
我相信你知道从这里做什么….哦,我不能帮助自己,这太可爱了：
SELECT
v.item_id, SUM(2 - v.vote) AS likes, SUM(v.vote - 1) AS dislikes
FROM (
SELECT
voter_id, item_id, MAX(vote_date) AS vote_date
FROM
votes
GROUP BY
voter_id, item_id
) tmp
INNER JOIN
votes v ON ( v.vote_date = tmp.vote_date
AND v.voter_id = tmp.voter_id
AND v.item_id = tmp.item_id
)
GROUP BY
v.item_id
结果：
+---------+-------+----------+
| item_id | likes | dislikes |
+---------+-------+----------+
| 9 | 3 | 1 |
| 10 | 1 | 0 |
+---------+-------+----------+
