使用 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 JOINing 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

dbfiddle 演示

这篇关于使用 LEFT JOIN 计数仅显示一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！