在MySQL中联接两个表 [英] Joining two tables in a MySQL
问题描述
我有下表称为投票":
我正在尝试将项目列表,用户表和投票表一起加入.
I'm trying to join a list of items, with a users table, and this votes table.
SELECT list_items.item_id, text, date_added, username
FROM list_items
NATURAL JOIN users, votes
WHERE list_id = 3
该查询给了我这个
我想获得每个list_item的总投票数,以及up_votes
的一列和down_votes
的一列.而且,当然,我不希望item_id重复这样.
I would like to get a total vote count for each list_item, as well a column for up_votes
and another for down_votes
. And, of course, I don't want the item_id's to repeat like that.
我尝试按照 Nettuts + 视频,但是教程太简单了.
I tried combining SUM with IF as explained in a Nettuts+ video, but the tutorial was too simple.
编辑:这是list_items表:
Here's the list_items table:
推荐答案
SELECT list_items.text, list_items.item_id, SUM(votes.vote=1) AS upvote, SUM(votes.vote=-1) AS downvote
FROM list_items
LEFT JOIN votes ON list_items.item_id = votes.item_id
棘手的部分是两个求和调用-如果表决字段为1
,则vote=1
的结果为TRUE,出于SUM()的目的,MySQL将其强制转换为整数1.如果它不是1,则它的计算结果为false,该值被强制转换为0,并且对SUM()不执行任何操作.
The tricky part are the two sum calls - If the vote field is 1
, then vote=1
which evaluates to TRUE, which MySQL will cast to an integer 1 for the purposes of the SUM(). If it's not 1, then it evaluates to false which is cast to a 0 and doesn't do anything for the SUM().
哇,需要拥有
GROUP BY list_items.item.id
最后.
这篇关于在MySQL中联接两个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!