mysql count 好几个表,多个表上的MySQL COUNT（*）

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.

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

解决方案

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