mysql - MySQL使用JOIN COUNT多个表

尝试使用联接从多个表中选择总计时遇到问题。COUNT的结果不正确。
我有三张桌子：

Customers
id -> Primary/Autoincrement
name

Documents
id -> Primary/Autoincrement
customer_id

Documents_items
id -> Primary/Autoincrement
document_id

我想获得按客户名称分组的文档和文档项的总数。

    SELECT cust.name,
           COUNT(doc.id),
           COUNT(item.id)
      FROM customers AS cust
INNER JOIN documents AS doc ON doc.customer_id = cust.id
INNER JOIN documents_items AS item ON item.document_id = doc.id
  GROUP BY cust.name

问题是COUNT(doc.id)的结果等于COUNT(item.id)的结果，这是不正确的。
您可以在SQLFiddle中看到错误的演示示例。
输入示例：

INSERT INTO customers VALUES('John')
INSERT INTO documents VALUES(1)
INSERT INTO documents_items VALUES(1), VALUES(1)

预期输出：

Name     |    Total Docs    | Total Items
John              1               2

电流输出：

Name     |    Total Docs    | Total Items
John              2               2

最佳答案

您想要count the distinct文档ID和项目ID。

    SELECT cust.name,
           COUNT(DISTINCT doc.id),
           COUNT(DISTINCT item.id)
      FROM customers AS cust
INNER JOIN documents AS doc ON doc.customer_id = cust.id
INNER JOIN documents_items AS item ON item.document_id = doc.id
  GROUP BY cust.name