我有一个'文件夹'表.我想返回userId为16的所有记录.
SELECT * FROM `folders` WHERE userId = 16;
我有一张'文件'表.对于上面返回的每个"文件夹",我想返回该"文件夹"中的"文件"计数.
SELECT COUNT(*) as "Files" FROM files WHERE Folder = n;
我如何结合这些?我迷路了.谢谢!
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
您可能需要使用GROUP BY并按ID或其分组:
SELECT folders.*, COUNT(files.*) as filetotal FROM folders LEFT JOIN files ON folders.ID=files.folderID WHERE userId = 16 GROUP BY folders.ID