在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屋！