MySQL查询左连接的两个表-得到错误的结果

06011991 发表于 Dev

06011991

我用左联接联接两个表，以得到总和结果，但是给出错误的结果。这是我的

table1
    | id | event | currency | amount |
    |----|-------|----------|--------|
    | 1  | HSA   | USD      | 2000   |
    | 2  | DMME  | USD      | 3000   |
    | 3  | HSI   | INR      | 1500   |
    | 4  | HSI   | INR      | 1500   |

表2

| id | table1_id | rcvamount | adjamount |
|----|-----------|-----------|-----------|
| 1  | 1         | 1980      | 20        |
| 2  | 2         | 1000      | 180       |
| 3  | 2         | 1500      | 20        |
| 4  | 4         | 1487      | 13        |

这是我的查询

SELECT
T1.event,T1.currency,
SUM(T1.total) AS Totalvalue,

SUM(T1.received) AS Received, 
(T1.bal) AS balance
FROM (SELECT
      table1.id,
      table1.amount AS total,

      SUM(table2.rcvamount+table2.adjamount) AS received,
      ((table1.amount)- sum(table2.rcvamount+table2.adjamount)) AS bal,
      table1.event,  
      table1.currency
      FROM table1 LEFT JOIN table2 ON table1.id=table2.table1_id 
     group by table1.id)T1
      Group By T1.event,T1.currency

但是，当我运行此查询时，给出如下错误结果。当涉及到名为HSI的第三个事件时，表2中的ID号3没有任何行。结果应按事件和货币分组。

| event | currency | Totalvalue | Received | balance |
|-------|----------|------------|----------|---------|
| DMME  | USD      | 3000       | 2700     | 300     |
| HSA   | USD      | 2000       | 2000     | 0       |
| HSI   | INR      | 3000       | 1500     | NULL    |

但是实际结果应该如下

| event | currency | Totalvalue | Received | balance |
|-------|----------|------------|----------|---------|
| DMME  | USD      | 3000       | 2700     | 300     |
| HSA   | USD      | 2000       | 2000     | 0       |
| HSI   | INR      | 3000       | 1500     | 1500    |

我没有弄错查询的地方。请帮助我进行排序。谢谢。

阿曼尼（Amani Ben Azzouz）

您正在使用，LEFT JOIN因此您将拥有所有第一张表行，即使它们在第二张表中没有通过所需的联接匹配。

在您的情况下，在第一个表中，id = 3在第二个表中不匹配，因此您将获得NULL结果。（SELECT * FROM table2 WHERE table1_id =3将给出0个结果）。

为了避免这种使用INNER JOIN：

SELECT T1.event, T1.currency, SUM( T1.total ) AS Totalvalue, 
       SUM( T1.received ) AS Received, ( T1.bal) AS balance
    FROM (
     SELECT table1.id, table1.amount AS total, 
     SUM( table2.rcvamount + table2.adjamount ) AS received, 
     ((table1.amount) - sum( table2.rcvamount + table2.adjamount ))AS bal,
     table1.event, table1.currency
     FROM table1
     INNER JOIN table2 ON table1.id = table2.table1_id
    GROUP BY table1.id
   )T1

 GROUP BY T1.event, T1.currency

更新：

试试这个，COALESCE()将每个转换null为0

SELECT T1.event, T1.currency, SUM( T1.total ) AS Totalvalue, SUM( T1.received ) AS Received, (
T1.bal
) AS balance
FROM (

SELECT table1.id, table1.amount AS total, SUM( COALESCE( table2.rcvamount, 0 ) + COALESCE( table2.adjamount, 0 ) ) AS received, (
COALESCE( table1.amount, 0 ) - sum( COALESCE( table2.rcvamount, 0 ) + COALESCE( table2.adjamount, 0 ) )
) AS bal, table1.event, table1.currency
FROM table1
LEFT JOIN table2 ON table1.id = table2.table1_id
GROUP BY table1.id
)T1
GROUP BY T1.event, T1.currency

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。