使用 LEFT JOIN 计数仅显示一行 [英] Count with LEFT JOIN shows only one row
问题描述
我的数据库中有以下数据:
I have the following data in my database:
scu_banks:
---------------------------------
| id | type | name |
|-------------------------------|
| 1 | 1 | One |
| 2 | 1 | Two |
| 3 | 2 | Three |
| 4 | 3 | Four |
---------------------------------
scu_statement:
scu_statement:
---------------------------------
| id | code | status |
|-----------------------------------|
| 1 | 1 | 0 |
| 2 | 1 | 1 |
| 3 | 2 | 0 |
| 4 | 1 | 0 |
-------------------------------------
我想要做的是选择表 scu_banks
中的所有行并计算状态为 0 的行数.数据应表示为:
What I want to do is I want to select all the rows in table scu_banks
and calculate how many rows I have with the status 0. The data should be represented like:
--------------------------------------------------------------
| scu_banks.type | scu_banks.name | status | scu_banks.id |
--------------------------------------------------------------
| 1 | One | 2 | 1 |
| 1 | Two | 0 | 2 | //There is no row with status 0
| 2 | Three | 0 | 3 |
| 3 | Four | 0 | 4 |
--------------------------------------------------------------
当我运行我的 sql 语句时,我得到以下数据:
When I run my sql statement I get the following data:
---------------------------------------------------------------
| scu_banks.type | scu_banks.name | status | scu_banks.id |
--------------------------------------------------------------
| 1 | One | 2 | 1 |
---------------------------------------------------------------
我在这种情况下得到的数据是正确的.2 它是表 scu_statement
中所有行的总数.该语句也不显示数据库中的其他行.
The data I get in this case is correct. 2 it the total count of all the rows in table scu_statement
. The statement also dont shows the other rows in the database.
有人知道我的sql语句有什么问题吗?
Does someone know what is wrong with my sql statement?
这是我的sql语句:
SELECT b.type 'scu_banks.type',
b.name 'scu_banks.name',
count(y.status) 'status',
b.id 'scu_banks.id'
FROM scu_banks b
LEFT JOIN (SELECT s.code, count(s.status) status
FROM scu_bankstatement s
WHERE status='0'
GROUP BY s.code) y
ON y.code = b.id
推荐答案
您需要在外部查询中使用 GROUP BY
,否则查询只会计算所有银行的状态.您还可以通过 LEFT JOIN
将代码/id 和 status = 0 上的两个表简化
You need a GROUP BY
in your outer query, otherwise the query simply counts status for all banks. You can also simplify your query by just LEFT JOIN
ing the two tables on code/id and status = 0
SELECT b.type `scu_banks.type`,
b.name `scu_banks.name`,
COUNT(s.status) `status`,
b.id `scu_banks.id`
FROM scu_banks b
LEFT JOIN scu_statement s ON s.code = b.id AND s.status = 0
GROUP BY b.id, b.name, b.type
输出
scu_banks.type scu_banks.name status scu_banks.id
1 One 2 1
1 Two 1 2
2 Three 0 3
3 Four 0 4
这篇关于使用 LEFT JOIN 计数仅显示一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!