MySQL 从一个表中选择，从另一个 id 链接的表中计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5971153/
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
select from one table, count from another where id's linked
提问by scarhand
heres my code:
继承人我的代码：
$sql = mysql_query("select c.name, c.address, c.postcode, c.dob, c.mobile, c.email,
count(select * from bookings where b.id_customer = c.id) as purchased, count(select * from bookings where b.the_date > $now) as remaining,
from customers as c, bookings as b
where b.id_customer = c.id
order by c.name asc");
you can see what i am trying to do, but im not sure how to write this query properly.
您可以看到我正在尝试做什么，但我不确定如何正确编写此查询。
heres the error i get:
这是我得到的错误：
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource
警告：mysql_fetch_assoc()：提供的参数不是有效的 MySQL 结果资源
heres my mysql_fetch_assoc:
继承人我的 mysql_fetch_assoc：
<?php
while ($row = mysql_fetch_assoc($sql))
{
?>
<tr>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['mobile']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo $row['purchased']; ?></td>
<td><?php echo $row['remaining']; ?></td>
</tr>
<?php
}
?>
回答by Will A
Try changing the likes of...
尝试改变...
count(select * from bookings where b.id_customer = c.id)
...to...
...到...
(select count(*) from bookings where b.id_customer = c.id)
回答by Andriy M
Your query incorrectly uses COUNT, which has been covered by @Will A's answer.
您的查询错误地使用了 COUNT，@Will A's answer已经涵盖了这一点。
I would also like to suggest a possibly better constructed alternative, which, I think, reflects the same logic:
我还想提出一个可能更好构造的替代方案，我认为它反映了相同的逻辑：
SELECT
c.name,
c.address,
c.postcode,
c.dob,
c.mobile,
c.email,
COUNT(*) AS purchased,
COUNT(b.the_date > $now OR NULL) AS remaining
FROM customers AS c
INNER JOIN bookings AS b ON b.id_customer = c.id
GROUP BY c.id
ORDER BY c.name ASC
Note: Normally you are expected to include all the non-aggregated SELECT expressions into GROUP BY. However MySQL supports shortened GROUP BY lists, so it's enough to specify the key expressions that uniquely identify all the non-aggregated data you are pulling. Please avoid using the feature arbitrarily. If a column not included in GROUP BY has more than one value per group, you have no controlover which value will actually be returned when pulling that column without aggregation.
注意：通常您应该将所有非聚合的 SELECT 表达式包含到 GROUP BY 中。但是 MySQL 支持缩短的 GROUP BY 列表，因此指定唯一标识您正在提取的所有非聚合数据的键表达式就足够了。请避免随意使用该功能。如果未包含在 GROUP BY 中的列每组具有多个值，则您无法控制在没有聚合的情况下拉动该列时实际返回的值。
