多个表上的 MySQL 连接和 COUNT()

我正在尝试在一个查询中对多个表进行 COUNT()，但我无法让它工作.这是我到目前为止所拥有的:

表格:

table1---------------------身份证 |名称---------------------1 |测试2 |测试2表2---------------------身份证 |表1_id---------------------1 |12 |13 |1表3---------------------身份证 |table2_id---------------------1 |1表4---------------------身份证 |尺寸 |table3_id---------------------1 |1024 |11 |200 |1

SQL:

SELECTtable1.name，COUNT(table2.table1_id) AS table2_count，COUNT(table3.table2_id) AS table3_count，COUNT(table4.table3_id) AS table4_count，SUM(table4.size) AS table4_size从表格1左连接表2ON table1.id = table2.table1_id左连接表3ON table2.id = table3.table2_id左连接表4ON table3.id = table4.table3_id在哪里table1.id = 1

我从上述查询中得到的结果:

名称 |table2_count |table3_count |table4_count |table4_size---------------------------------------------------------------测试 |4 |2 |2 |1224

我应该得到的结果:

名称 |table2_count |table3_count |table4_count |table4_size---------------------------------------------------------------测试 |3 |1 |2 |1224

解决方案

您将需要使用 DISTINCT，但您还需要计算 ID，而不是外键:

SELECTtable1.name，COUNT(DISTINCT table2.id) AS table2_count，COUNT(DISTINCT table3.id) AS table3_count，COUNT(DISTINCT table4.id) AS table4_count，SUM(table4.size) AS table4_size从表 1LEFT JOIN table2 ON table1.id = table2.table1_idLEFT JOIN table3 ON table2.id = table3.table2_idLEFT JOIN table4 ON table3.id = table4.table3_id哪里 table1.id = 1

这是一个小提琴.

说明:DISTINCT 关键字消除了所有重复值，从而生成唯一值列表.

如果你在没有 COUNT() 和 SUM() 的情况下运行你的查询，你会得到:

<前>名称 table1_id table2_id table3_id 大小测试 1 1 1 1024测试 1 1 1 200测试 1(空)(空)(空)测试 1(空)(空)(空)

因此，如果您添加 COUNT() 和 SUM()，您显然会得到:

<前>名称 table1_id table2_id table3_id 大小测试 4 2 2 1224

但是，在查询中使用 DISTINCT 将无济于事，因为您可以清楚地看到重复的值，这将导致:

<前>名称 table1_id table2_id table3_id 大小测试 1 1 1 1224

现在，如果你在没有 COUNT() 和 SUM() 的情况下运行我的查询，你会得到:

<前>名称 table1_id table2_id table3_id 大小测试 1 1 1 1024测试 1 1 2 200测试 2(空)(空)(空)测试 3(空)(空)(空)

如果您添加 COUNT() 和 SUM()，您将获得与查询完全相同的结果:

<前>名称 table1_id table2_id table3_id 大小测试 4 2 2 1224

然而，因为这次你有不同的值(即不是所有的都是 1)，所以现在如果你使用 DISTINCT 计算唯一值，你会得到:

<前>名称 table1_id table2_id table3_id 大小测试 3 1 2 1224

I'm trying to COUNT() on multiple tables in one query, but I can't get it to work. Here's what I have so far:

TABLES:

table1
---------------------
id | name
---------------------
 1 | test
 2 | test2


table2
---------------------
id | table1_id
---------------------
 1 | 1
 2 | 1
 3 | 1


table3
---------------------
id | table2_id
---------------------
 1 | 1


table4
---------------------
id | size | table3_id
---------------------
 1 | 1024 | 1
 1 | 200  | 1

SQL:

SELECT
    table1.name,
    COUNT(table2.table1_id) AS table2_count,
    COUNT(table3.table2_id) AS table3_count,
    COUNT(table4.table3_id) AS table4_count,
    SUM(table4.size) AS table4_size
FROM
    table1
LEFT JOIN table2
    ON table1.id = table2.table1_id
LEFT JOIN table3
    ON table2.id = table3.table2_id
LEFT JOIN table4
    ON table3.id = table4.table3_id
WHERE
    table1.id = 1

Results I'm getting from the above query:

name | table2_count | table3_count | table4_count | table4_size
---------------------------------------------------------------
test |      4       |      2       |      2       |    1224

Results that I should be getting:

name | table2_count | table3_count | table4_count | table4_size
---------------------------------------------------------------
test |      3       |      1       |      2       |    1224

解决方案

You will need to use DISTINCT, but also you need to count the IDs, not the foreign keys:

SELECT
    table1.name,
    COUNT(DISTINCT table2.id) AS table2_count,
    COUNT(DISTINCT table3.id) AS table3_count,
    COUNT(DISTINCT table4.id) AS table4_count,
    SUM(table4.size) AS table4_size
FROM table1
LEFT JOIN table2 ON table1.id = table2.table1_id
LEFT JOIN table3 ON table2.id = table3.table2_id
LEFT JOIN table4 ON table3.id = table4.table3_id
WHERE table1.id = 1

Here is a fiddle.

Explanation: The DISTINCT key word eliminates all duplicate values resulting in a list of unique values.

If you run your query without the COUNT() and SUM(), you get:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1024 
test  1          1          1          200 
test  1          (null)     (null)     (null) 
test  1          (null)     (null)     (null) 

So if you add the COUNT() and SUM(), you obviously get:

name  table1_id  table2_id  table3_id  size
test  4          2          2          1224 

However, using DISTINCT with your query won't help because you can clearly see the duplicate values, which will result in:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1224 

Now, if you run my query without the COUNT() and SUM(), you get:

name  table1_id  table2_id  table3_id  size
test  1          1          1          1024 
test  1          1          2          200 
test  2          (null)     (null)     (null) 
test  3          (null)     (null)     (null) 

If you add the COUNT() and SUM(), you get exactly the same results like your query:

name  table1_id  table2_id  table3_id  size
test  4          2          2          1224 

However, because this time you have different values (i.e. not all are 1), so now if you count the unique values using DISTINCT, you get:

name  table1_id  table2_id  table3_id  size
test  3          1          2          1224