MySQL计数多个表的连接

I have a problem trying to select the total from multiple tables using joins. The results of COUNT are not correct.

我在尝试使用连接从多个表中选择total时遇到了问题。计数结果不正确。

I have three tables:

我有三个表:

Customers
id -> Primary/Autoincrement
name

Documents
id -> Primary/Autoincrement
customer_id

Documents_items
id -> Primary/Autoincrement
document_id

And I would like to obtain the total, grouped by customer name, of documents and documents items.

我想要按客户名称、文件和文件项来分组。

    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

The problem is that the result of COUNT(doc.id) is equal to the result of COUNT(item.id) which is not correct.

问题是COUNT(doc.id)的结果等于COUNT(item.id)的结果，这是不正确的。

You can see a demo example of the error in SQLFiddle.

您可以看到SQLFiddle中的错误示例。

Input example:

输入的例子:

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

Output expected:

输出预期:

Name     |    Total Docs    | Total Items
John              1               2

Current output:

电流输出:

Name     |    Total Docs    | Total Items
John              2               2

2 个解决方案

#1

You want to count the distinct document id's and item id's.

您需要计算不同的文档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

#2

Try this way:

试试这种方法:

SELECT T1.name,T1.Docs,T2.Items
FROM 
( SELECT cust.name, COUNT(doc.id) as Docs
  FROM customers AS cust
  INNER JOIN documents AS doc ON doc.customer_id = cust.id
  GROUP BY cust.name) T1 JOIN

( SELECT cust.name, COUNT(item.id) as Items
  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) ON T1.name =T2.name

Explanation:

解释:

You have to generate two result with each count. And then join those result with name field.

每个计数必须生成两个结果。然后用name字段连接这些结果。

First inner query will generate the name and count of docs. Second inner query will generate the name and count of items. Then we will join those queries on the name field.

第一个内部查询将生成文档的名称和计数。第二个内部查询将生成项的名称和计数。然后我们将在name字段中加入这些查询。

#1