MySQL 查询二 (COUNT)

Question

我在使用 COUNT 时遇到了一些麻烦。我需要将文件和文件夹计数到存储中。这是我的结构。

DATABASE

STORAGE
  storage_id    storage_name
  1             SERVER 01


FOLDERS
  folder_id   folder_storage_id   folder_name
  1           1                   EXAMPLE FOLDER 

FILES
  file_id    file_folder_id    file_name
  1          1                 test.pdf
  2          1                 test2.pdf
  3          1                 test3.pdf
  4          1                 test4.pdf


QUERY

SELECT  *,

folder_storage_id AS folder_id, COUNT(*) AS folder_qty,

file_folder_id AS file_id, COUNT(*) AS files_qty

FROM
files

LEFT JOIN
folders ON folder_id = file_folder_id

LEFT JOIN
storage ON storage_id = folder_storage_id

我收到 $folder_qty 和 $files_qty 相同的值 (4)

预期

($folder_qty) 1

($files_qty) 4

Answer 1

试试这个:

SELECT  
   COUNT(DISTINCT folder_storage_id) AS folder_qty,
   COUNT(*) AS files_qty
FROM
   files
LEFT JOIN
   folders ON folder_id = file_folder_id
LEFT JOIN
   storage ON storage_id = folder_storage_id
WHERE 
   storage_id = ?

选择任何其他列没有多大意义，因为执行了聚合。