INNER JOIN仅在表格中显示第一行 [英] INNER JOIN shows only first row in table

问题描述

我的数据库中有以下数据:

I have the following data in my database:

scu_banks:

scu_banks:

---------------------------------
|   id    |   type   |   name   |
|-------------------------------|  
|    1    |    1     |   One    |
|    2    |    1     |   Two    |
|    3    |    2     |  Three   |
|    4    |    3     |   Four   |
---------------------------------

scu_statement:

scu_statement:

---------------------------------
|   id    |   code   |   mutation   |
|-----------------------------------|  
|    1    |    1     |     100      |
|    2    |    1     |     200      |
|    3    |    2     |     500      |
|    4    |    1     |     500      |
-------------------------------------

我想做的是我要选择表scu_banks中的所有行，并显示突变的总和.数据应表示为:

What I want to do is I want to select all the rows in table scu_banks and show the total sum of mutations. The data should be represented like:

--------------------------------------------------------------
| scu_banks.type | scu_banks.name |   total   | scu_banks.id |
--------------------------------------------------------------
|       1        |      One       | € 800.00  |      1       |
|       1        |      Two       | € 500.00  |      2       |
|       2        |     Three      | €   0.00  |      3       |
|       3        |      Four      | €   0.00  |      4       |
--------------------------------------------------------------

当我运行sql语句时，我得到以下数据:

When I run my sql statement I get the following data:

---------------------------------------------------------------
| scu_banks.type | scu_banks.name |    total   | scu_banks.id |
--------------------------------------------------------------
|       1        |      One       | € 1300.00  |      1       |
---------------------------------------------------------------

在这种情况下，我得到的数据不正确. €1300.00它是表scu_statement中所有突变的总和.该语句也不会显示数据库中的其他行.

The data I get in this case is not correct. € 1300.00 it the total of all the mutations 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      scu_banks.type,
            scu_banks.name, 
            CONCAT('€ ', FORMAT(IFNULL(SUM(scu_statement.mutations), 0),2)) AS total, 
            scu_banks.id
FROM        scu_banks
INNER JOIN  scu_statement
ON          scu_banks.id = scu_statement.code

推荐答案

在子查询中进行汇总，然后将其加入银行.

Do the aggregation in a subquery and left join it to the banks.

SELECT b.type "scu_banks.type",
       b.name "scu_banks.name",
       concat('€ ', format(coalesce(x.mutation, 0), 2)) "total",
       b.id "scu_banks.id"
       FROM scu_banks b
            LEFT JOIN (SELECT s.code,
                              sum(s.mutation) mutation
                              FROM scu_statement s
                              GROUP BY s.code) x
                      ON x.code = b.id;

这篇关于INNER JOIN仅在表格中显示第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！