我有四个表:

表订单:

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


餐桌服务:

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的金额总和不正确,我认为这是由于连接。

最佳答案

您应该一直使用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中使用联接的更多理解

关于mysql - 连接多个表时,Sum()返回不正确的值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/47751203/

10-13 02:05
查看更多