SQL 双左连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8416967/
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
Double LEFT JOIN
提问by Dmitry Teplyakov
I want to receive property name and units count and specails count. I have this query:
我想接收属性名称和单位计数以及特殊计数。我有这个查询：
SELECT
`property`.`property_name`,
COUNT(unit_id) AS `units_count`,
COUNT(special_id) AS `specials_count`
FROM `property`
LEFT JOIN `property_unit` ON unit_property_id = property_id
LEFT JOIN `property_special` ON special_property_id = property_id
WHERE (property_id = '1')
GROUP BY `property_id`
ORDER BY `property_name` ASC
But it is not working properly. If I have one of these left joins - it's ok, but if I have two, I get this result:
但它不能正常工作。如果我有这些左连接之一 - 没关系，但如果我有两个，我会得到这个结果：
["property_name"] => string(11) "Rivers Edge"
["units_count"] => string(1) "2"
["specials_count"] => string(1) "2"
Specials count is 2 and units_count is 2, but units count is really '1'. How can I get correct counts for it?
特价商品计数为 2，units_count 为 2，但单位计数实际上为“1”。我怎样才能得到正确的计数？
P.S: for those who know Zend Framework:
PS：对于了解 Zend Framework 的人：
$select->setIntegrityCheck(FALSE)
->from(
'property',
array(
'property_name',
)
)
->joinLeft(
'property_unit',
'unit_property_id = property_id',
array(
'units_count' => 'COUNT(unit_id)'
)
)
->joinLeft(
'property_special',
'special_property_id = property_id',
array(
'specials_count' => 'COUNT(special_id)'
)
)
->group('property_id')
->order('property_name');
回答by
Try this:
尝试这个：
SELECT
`property`.`property_name`,
COUNT(distinct unit_id) AS `units_count`,
COUNT(distinct special_id) AS `specials_count`
FROM `property`
LEFT JOIN `property_unit` ON unit_property_id = property_id
LEFT JOIN `property_special` ON special_property_id = property_id
WHERE (property_id = '1')
GROUP BY `property_id`
ORDER BY `property_name` ASC
EDIT:
编辑：
You shouldn't alwaysuse distinct - it happens to be the right option in this case.
你不应该总是使用 distinct - 在这种情况下它恰好是正确的选择。
select count(fieldname)returns the number of times that fieldname is not null; select count(distinct fieldname)returns the number of distinct values of fieldname.
select count(fieldname)返回字段名不为空的次数；select count(distinct fieldname)返回 fieldname 的不同值的数量。
In the original query, property_unit and property_special aren't joined to each other, only to property - so for a single property that had 5 units and 7 specials, 35 rows would be returned; therefore count(unit_id)and count(special_id)would both return 35. Since there would be 5 distinct values of unit_id and 7 distinct values of special_id (becausethese fields uniquely identify their records), count(distinct ...)returns the correct values in these circumstances.
在原始查询中，property_unit 和 property_special 不相互连接，仅连接到属性 - 因此对于具有 5 个单元和 7 个特价的单个属性，将返回 35 行；因此，count(unit_id)并且count(special_id)都将返回 35。由于 unit_id 将有 5 个不同的值，special_id 将有 7 个不同的值（因为这些字段唯一地标识了它们的记录），count(distinct ...)因此在这些情况下返回正确的值。
回答by Sudhir Bastakoti
Your SQL should be something like this:
你的 SQL 应该是这样的：
SELECT `property`.`property_name`, COUNT(property_unit.unit_id) AS `units_count`, COUNT(property_special.special_id) AS `specials_count` FROM `property` LEFT JOIN `property_unit` ON (property_unit.unit_property_id = property.property_id) LEFT JOIN `property_special` ON (property_special.special_property_id = property.property_id) WHERE (property.property_id = '1') GROUP BY `property.property_id` ORDER BY `property.property_name` ASC
