多个表上的MySQL COUNT(*) [英] Mysql COUNT(*) on multiple tables

问题描述

此查询出了什么问题

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

换句话说，我该怎么做，所以从"courses"返回的每条记录都有 另一列名为"modCount"，显示该课程ID的模块表中的记录数，另一列名为"vidCount"，其对视频表执行相同的操作.

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.

错误:

错误号:1064

Error Number: 1064

您的SQL语法有错误； 检查对应的手册 您的MySQL服务器版本 在')附近使用的正确语法 moduleCount，vid.COUNT()作为vidCount 从作为合作伙伴的课程开始，在第1行

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

推荐答案

使用子选择可以执行以下操作:

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.

如果您的表很大，则以下查询应执行得更好(以使阅读和理解更为复杂).

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

这篇关于多个表上的MySQL COUNT(*)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！