MySQL联接和COUNT()在多个表上 [英] MySQL join and COUNT() on multiple tables
问题描述
我正在尝试在一个查询中对多个表进行 COUNT()
,但是无法正常工作.这是我到目前为止的内容:
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:
表格:
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
我应该得到的结果:
name | table2_count | table3_count | table4_count | table4_size
---------------------------------------------------------------
test | 3 | 1 | 2 | 1224
推荐答案
您将需要使用 DISTINCT
,但是您还需要计算ID,而不是外键:
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
这里是小提琴.
说明: DISTINCT
关键字可消除所有重复值,从而产生唯一值列表.
Explanation: The DISTINCT
key word eliminates all duplicate values resulting in a list of unique values.
如果在没有 COUNT()
和 SUM()
的情况下运行查询,则会得到:
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)
因此,如果添加 COUNT()
和 SUM()
,您显然会得到:
So if you add the COUNT()
and SUM()
, you obviously get:
name table1_id table2_id table3_id size
test 4 2 2 1224
但是,在查询中使用 DISTINCT
并没有帮助,因为您可以清楚地看到重复的值,这将导致:
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
现在,如果在没有 COUNT()
和 SUM()
的情况下运行我的查询,则会得到:
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)
如果添加 COUNT()
和 SUM()
,则会得到与查询完全相同的结果:
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
但是,由于这次您有不同的值(即,并非所有值都是1),所以现在,如果您使用 DISTINCT
来计算唯一值,则会得到:
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
这篇关于MySQL联接和COUNT()在多个表上的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!