SQL 结合连接和计数的 MySQL 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/598355/
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 statement combining a join and a count?
提问by Corey
I've got a table of 'folders'. I want to return all the records with the userId of 16.
我有一张“文件夹”表。我想返回 userId 为 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!
我如何结合这些？我迷路了。谢谢！
回答by jennykwan
SELECT fol.*
, ( SELECT COUNT(*)
FROM files fil
WHERE fil.Folder = fol.Folder
) AS "Files"
FROM folders fol
WHERE fol.userId = 16
It's called a correlated subquery.
它被称为相关子查询。
http://dev.mysql.com/doc/refman/5.1/en/correlated-subqueries.html
http://dev.mysql.com/doc/refman/5.1/en/correlated-subqueries.html
回答by dusoft
you would probably need to use GROUP BY and group it by ID or such:
您可能需要使用 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
回答by Ron Savage
Do a sub query that groups by the Folders to get the count per folder, then join it to the first query like this:
执行按文件夹分组的子查询以获取每个文件夹的计数，然后将其加入第一个查询，如下所示：
select
f.*
fc.Files
from
Folders f
--
-- Join the sub query with the counts by folder
JOIN (select
Folder,
count(*) Files
from
files
group by
Folder ) as fc
on ( fc.Folder = f.Folder )
where
f.userid = 16
回答by vortex
select
f.`folder`,
f.`userId`,
r.`count`
from
`folders` f
left join
(
select
`Folder`,
count(`id`) `count`
from `files`
group by `Folder`
) r
on r.`Folder`=f.`folder`
where
`userId`=16
if you do not want to use the group by statement.
如果您不想使用 group by 语句。
i had a simmilar problem in a product list where i wanted to count the star rating of a product in another table [1-5] and the amount of users that voted for that specific product.
我在产品列表中有一个类似的问题，我想计算另一个表 [1-5] 中产品的星级以及为该特定产品投票的用户数量。
