MySQL MySQL多左外连接查询问题涉及3个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3469643/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Multiple Left Outer Join Query Question involving 3 tables
提问by Mike K
Due to 0 responses, I'm guessing that my LEFT JOIN question got into too much detail about a database that was too esoteric. I've already programmed around the issue, but I'd still like to know how to join in a similar scenario:
由于 0 个回复，我猜我的 LEFT JOIN 问题涉及到太多关于太深奥的数据库的细节。我已经围绕这个问题进行了编程，但我仍然想知道如何加入类似的场景：
Assume a basic surrogate key strategy (each table has an id field that just auto-increments), as well as a foreign key to its obvious parent. Words in all caps can be considered tables.
假设一个基本的代理键策略（每个表都有一个自动递增的 id 字段），以及一个指向其明显父级的外键。所有大写的单词都可以视为表格。
Say you have a Database containing DOGS. Example: Wolfie, Winston, Butch, and Benny
假设您有一个包含 DOGS 的数据库。示例：沃尔夫、温斯顿、布奇和本尼
Each DOG has FLEAs. (for simplicity lets make it so that one flea lives on only one dog and leave this a 1 to many relationship). The fleas have id's as names or whatever, along with what colorthey are.
每只狗都有跳蚤。（为了简单起见，让一只跳蚤只生活在一只狗身上，并保持一对多的关系）。跳蚤有 id 作为名字或其他什么，以及它们是什么颜色。
Each FLEA will BITE it's DOG host several times, and that is stored in this database, and recorded daily.
Fields id(PK), flea id(FK), date, times_bitten.
每只 FLEA 都会多次咬它的 DOG 宿主，并将其存储在此数据库中，并每天记录。
字段 id(PK)、跳蚤 id(FK)、日期、times_bitten。
Say you want to get the total number of times each dog was bitten (this is easy)
假设您想获得每只狗被咬的总次数（这很容易）
SELECT Dog.Name, sum(Bite.times_bitten)
FROM Dog, Flea, Bite
WHERE Dog.id = Flea.Dog_id and Bite.id = Flea.Bite_id
GROUP BY Dog.Name
Let's say that you were to add criteria to the "WHERE" clause limiting it to "Brown" fleas, and no "Brown" Fleas ever bit Benny.
假设您要向“WHERE”子句添加条件，将其限制为“棕色”跳蚤，并且“棕色”跳蚤不会咬 Benny。
SELECT Dog.Name, sum(Bite.times_bitten)
FROM Dog, Flea, Bite
WHERE Dog.id = Flea.Dog_id and Bite.id = Flea.Bite_id and Flea.color = "Brown"
GROUP BY Dog.Name
Benny is not in the result
本尼不在结果中
How would you rewrite the query so that Benny's name would still show up, with either a 0(preferably) or NULL in the sum filed, rather than just having Benny eliminated altogether from the result?
您将如何重写查询，以便 Benny 的名字仍会显示，并且总和字段中为 0（最好）或 NULL，而不是仅将 Benny 从结果中完全消除？
This seems like its about multiple left outer joins.. but with multiple tables like this, the documentation that's readily findable doesn't seem to answer a question involving 3 tables with a value filter in the middle of the 3 tables.
这似乎是关于多个左外连接..但是对于这样的多个表，很容易找到的文档似乎没有回答涉及 3 个表的问题，并且在 3 个表的中间有一个值过滤器。
Does anyone have advice on how to rewrite this to allow for multiple left outer joins, or have some other method of keeping all of the dog's names in the query even if the sum = 0?
有没有人有关于如何重写它以允许多个左外连接的建议，或者即使总和 = 0也有一些其他方法可以在查询中保留所有狗的名字？
回答by Daniel Vandersluis
This should work:
这应该有效：
SELECT Dog.Name, COALESCE(SUM(Bite.times_bitten), 0) AS times_bitten
FROM Dog
LEFT JOIN Flea
ON Flea.Dog_id = Dog.id
AND Flea.color = "Brown"
LEFT JOIN Bite
ON Bite.id = Flea.Bite_id
GROUP BY Dog.Name
By using LEFT JOINs, you will pull all the Dog records, even those without corresponding Fleas (for which the columns from the join will be NULL). You can use COALESCEto set times_bittento 0 if no records are found (otherwise it would be NULL).
通过使用LEFT JOINs，您将拉取所有 Dog 记录，即使是那些没有相应跳蚤的记录（联接中的列将为 NULL）。如果没有找到记录，您可以使用COALESCE将其设置times_bitten为 0（否则它将为 NULL）。
You probably also want to group by Dog.idinstead of Dog.Name(unless it is impossible for there to be multiple dogs with the same name?)
你可能还想分组Dog.id而不是Dog.Name（除非不可能有多只同名的狗？）
