主页 / dba / 问题 / 84824
Accepted
Ismail Gunes
Asked: 2014-12-11 09:43:43 +0800 CST

如何获得多于一张表的总和

  • 772

我有 3 个表，如链接 sqlfiddle 中所示 当我对两个表求和时，我得到了很好的结果 sqlfiddle

但是当我添加第三个表并且它是查询时，我得到了意想不到的结果

http://sqlfiddle.com/#!2/f9858a/12

我该如何解决这个问题？

mysql sum

2 个回答

  1. Best Answer

    当你在没有的情况下进行聚合时ingredient，然后 LEFT JOIN 结果到成分你得到这个

    mysql> SELECT A.*,ig.qty FROM
    -> (SELECT dm.id,dm.nom,
    -> SUM(IF(dm.id=sd.matid, quantite,0)) as thisqty,
    -> SUM(IF(dm.id=sd.matid, sd.montant * (1+sd.tvaval/100),0)) AS thissum
    -> FROM stockdata AS sd
    -> JOIN defmatiere as dm ON dm.id=sd.matid
    -> WHERE dm.type=1 GROUP BY dm.nom ORDER BY dm.id) A
    -> LEFT JOIN ingredient as ig ON ig.matid=A.id;
+----+----------+---------+--------------------+------+
| id | nom      | thisqty | thissum            | qty  |
+----+----------+---------+--------------------+------+
|  1 | Amande   |   19470 | 204.74464999999998 |  600 |
|  1 | Amande   |   19470 | 204.74464999999998 |  200 |
|  2 | Chocolat |    7920 |              62.97 |  100 |
|  2 | Chocolat |    7920 |              62.97 |  200 |
|  4 | Beurre   |     500 |               2.48 |   60 |
|  4 | Beurre   |     500 |               2.48 |   60 |
+----+----------+---------+--------------------+------+
6 rows in set (0.00 sec)

mysql>

    也许在 INNER JOIN 之后进行聚合会有所帮助

    mysql> SELECT A.*,SUM(ig.qty) igqty FROM
    -> (SELECT dm.id,dm.nom,
    -> SUM(IF(dm.id=sd.matid, quantite,0)) as thisqty,
    -> SUM(IF(dm.id=sd.matid, sd.montant * (1+sd.tvaval/100),0)) AS thissum
    -> FROM stockdata AS sd
    -> JOIN defmatiere as dm ON dm.id=sd.matid
    -> WHERE dm.type=1 GROUP BY dm.nom ORDER BY dm.id) A
    -> LEFT JOIN ingredient as ig ON ig.matid=A.id
    -> GROUP BY A.id,A.nom,A.thisqty,A.thissum;
+----+----------+---------+--------------------+-------+
| id | nom      | thisqty | thissum            | igqty |
+----+----------+---------+--------------------+-------+
|  1 | Amande   |   19470 | 204.74464999999998 |   800 |
|  2 | Chocolat |    7920 |              62.97 |   300 |
|  4 | Beurre   |     500 |               2.48 |   120 |
+----+----------+---------+--------------------+-------+
3 rows in set (0.00 sec)

mysql>
    • 1

  2. 但是我的成分表（可以在 slqfiddle 中看到）不适合这个查询。最后，我在成分表中添加了一个日期列，这样我就可以按预期使用日期条件进行检索。

    结果在链接中

    sqlfiddle

    不添加日期列的任何想法将不胜感激。

    • 0

相关问题