mysql - 连接多个表时 Sum() 返回错误值

Question

我有四张 table :

表订单:

o_id    o_c_id  o_type      o_date
1       23    TA          2015-01-01
2       24    TA          2015-01-16
3       25    GA          2015-01-08
4       26    TA          2016-01-24
5       26    FB          2016-01-28

表客户:

c_id    c_name  c_email
23       Ander   xxx@gmail.com          
24       Kay     xxx@gmail.com
25       Bob     xxx@gmail.com
26       Devi    xxx@gmail.com

餐 table 服务:

s_o_id    s_name  s_nr  s_amount
1       SGHH   75454645   350     
1       SGHH   75454645   420
2       TAK    74322411   214
3       BGH    74288442   850

表外部:

ext_id ext_name ext_nr  ext_amount
1      Gerry   75454645   350
1      Gerry   75454645   420
2      Alby    74322411   214
3      Alby    74288442   850

结果应该如下所示:

| c_name   | o_date        | ext_name |  ext_nr  |   s_nr    | ext_amount | s_amount |
|----------|---------------|----------|----------|-----------|------------|----------|
| Ander    | 2016-09-19    | Gerry    | 75454645 | 75454645  |   760      |  730     |

我的查询:

SELECT c.c_name
     , o.o_date 
     , s.s_name
     , ext.ext_nr
     , s.s_nr
     , SUM(ext.ext_amount) 
     , SUM(s.s_amount) 
  FROM orders o
  JOIN customer c 
    ON o.c_id = c.c_id
  JOIN services s 
    ON s.o_id = o.o_id
 RIGHT 
  JOIN external ext 
    ON ext.ext_nr = s.s_nr
 GROUP 
    BY s.s_nr;

返回的不同 nr 的金额总和不正确，我认为这是由于连接所致。

Answer 1

您应该始终使用 JOIN (默认为 INNER JOIN)，并添加 group by 子句中除聚合列之外的所有列:

SELECT c.c_name
 , o.o_date 
 , s.s_name
 , ext.ext_nr
 , s.s_nr
 , SUM(ext.ext_amount) 
 , SUM(s.s_amount) 
FROM orders o
JOIN customer c 
ON o.c_id = c.c_id
JOIN services s 
ON s.o_id = o.o_id
JOIN external ext 
ON ext.ext_nr = s.s_nr
GROUP BY
   c.c_name
 , o.o_date 
 , s.s_name
 , ext.ext_nr
 , s.s_nr;

有关在 sql 中使用联接的更多了解 click here