结合连接和计数的 MySQL 语句? [英] MySQL statement combining a join and a count?

问题描述

我有一张文件夹"表.我想返回 userId 为 16 的所有记录.

I've got a table of 'folders'. I want to return all the records with the userId of 16.

SELECT * FROM `folders` WHERE userId = 16;

我有一张文件"表.对于上面返回的每个文件夹"，我想返回该文件夹"中的文件"计数.

I've got a table of 'files'. For each 'folder' returned above, I want to return a count of 'files' within that 'folder'.

SELECT COUNT(*) as "Files" FROM files WHERE Folder = n;

我该如何组合这些?我迷路了.谢谢！

How do I combine these? I'm lost. Thanks!

推荐答案

SELECT  fol.*
 ,      (       SELECT  COUNT(*)
                FROM    files           fil
                WHERE   fil.Folder      = fol.Folder
        )       AS      "Files"
FROM    folders         fol
WHERE   fol.userId      = 16

它被称为相关子查询.

http://dev.mysql.com/doc/refman/5.1/en/correlated-subqueries.html

这篇关于结合连接和计数的 MySQL 语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！