多个表上的 Mysql COUNT(*)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/944099/
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 COUNT(*) on multiple tables
提问by Click Upvote
What's wrong with this query:
这个查询有什么问题：
SELECT co.*, mod.COUNT(*) as moduleCount, vid.COUNT(*) as vidCount
FROM courses as co, modules as mod, videos as vid
WHERE mod.course_id=co.id AND vid.course_id=co.id ORDER BY co.id DESC
In other words, how can I do it so with every record returned from 'courses', there's an additional column called 'modCount' which shows the number of records in the modules table for that course_id, and another called 'vidCount' which does the same thing for the videos table.
换句话说，我该如何处理从“courses”返回的每条记录，还有一个名为“modCount”的附加列，它显示模块表中该 course_id 的记录数，另一个名为“vidCount”的列执行视频表也是如此。
Error:
错误：
Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as moduleCount, vid.COUNT() as vidCount FROM courses as co, ' at line 1
错误编号：1064
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行附近使用的正确语法 ' ) as moduleCount, vid.COUNT() as vidCount FROM course as co, '
回答by Stefan Gehrig
Using subselects you can do:
使用子选择，您可以执行以下操作：
SELECT co.*,
(SELECT COUNT(*) FROM modules mod WHERE mod.course_id=co.id) AS moduleCount,
(SELECT COUNT(*) FROM videos vid WHERE vid.course_id=co.id) AS vidCount
FROM courses AS co
ORDER BY co.id DESC
But be carefull as this is an expensive query when courses has many rows.
但要小心，因为当课程有很多行时，这是一个昂贵的查询。
EDIT:If your tables are quite large the following query should perform much better (in favor of being more complex to read and understand).
编辑：如果您的表非常大，则以下查询应该表现得更好（有利于阅读和理解更复杂）。
SELECT co.*,
COALESCE(mod.moduleCount,0) AS moduleCount,
COALESCE(vid.vidCount,0) AS vidCount
FROM courses AS co
LEFT JOIN (
SELECT COUNT(*) AS moduleCount, course_id AS courseId
FROM modules
GROUP BY course_id
) AS mod
ON mod.courseId = co.id
LEFT JOIN (
SELECT COUNT(*) AS vidCount, course_id AS courseId
FROM videos
GROUP BY course_id
) AS vid
ON vid.courseId = co.id
ORDER BY co.id DESC
回答by wasim
i have better solution and easy
我有更好的解决方案而且很容易
SELECT COUNT(*),(SELECT COUNT(*) FROM table2) FROM table1
回答by Quassnoi
SELECT co.*,
(
SELECT COUNT(*)
FROM modules mod
WHERE mod.course_id = co.id
) AS modCount,
(
SELECT COUNT(*)
FROM videos vid
WHERE vid.course_id = co.id
) AS vidCount
FROM courses co
ORDER BY
co.id DESC
回答by Dave Markle
SELECT co.*, m.ModCnt as moduleCount, v.VidCnt as vidCount
FROM courses co
INNER JOIN (
select count(*) AS ModCnt, co.id AS CoID
from modules
group by co) m
ON m.CoID = co.id
INNER JOIN (
select count(*) AS VidCnt, co.id AS CoID
from videos
group by co) v
ON v.CoID = co.id
INNER JOIN videos vid
ON vid.course_id = co.id
ORDER BY co.id DESC
回答by Click Upvote
Shoot this. I did the job with some non-mysql code:
拍这个。我用一些非 mysql 代码完成了这项工作：
function getAllWithStats($info='*',$order='',$id=0)
{
$courses=$this->getAll($info,$order,$id);
foreach ($courses as $k=>$v)
{
$courses[$k]['modCount']=$this->getModuleCount($v['id']);
$courses[$k]['vidCount']=$this->getVideoCount($v['id']);
}
return $courses;
}
