select语句将一个表中的列相乘，然后从另一个表中添加列

mysql> select * from orders;
+---------+------------+------------+------------+----------+----------+
| orderid | customerid | orderdate  | shipdate   | shipping | salestax |
+---------+------------+------------+------------+----------+----------+
|       1 |          1 | 2008-03-31 | 2008-03-31 |     4.99 |     5.00 | 
|       2 |          1 | 2008-04-01 | 2008-04-02 |     5.99 |     5.00 | 
|       3 |          2 | 2008-04-01 | 2008-04-02 |     3.99 |     6.00 | 
|       4 |          3 | 2008-04-02 | 2008-04-02 |     6.99 |     7.50 | 
+---------+------------+------------+------------+----------+----------+
4 rows in set (0.02 sec)
mysql> select * from orderinfo;
+---------+------------+-----+-------+----------+
| orderid | isbn       | qty | price | detailid |
+---------+------------+-----+-------+----------+
|       1 | 0929306279 |   1 | 29.95 |        1 | 
|       1 | 0929306260 |   1 | 49.95 |        2 | 
|       2 | 0439357624 |   3 | 16.95 |        3 | 
|       3 | 0670031844 |   1 | 34.95 |        4 | 
|       4 | 0929306279 |   1 | 29.95 |        5 | 
|       4 | 0929306260 |   1 | 49.95 |        6 | 
|       4 | 0439357624 |   1 | 16.95 |        7 | 
|       4 | 0670031844 |   1 | 34.95 |        8 | 
+---------+------------+-----+-------+----------+
8 rows in set (0.00 sec)

im trying to multiply the qty and price from the orderinfo table then add the shipping from the order table when i try to multiple them it works fine but when I add the shipping I get an incorrect value

即时尝试将orderty表中的数量和价格相乘,然后在我尝试复制它们时从订单表中添加运费它工作正常但是当我添加运费时我得到的值不正确

this works

mysql> select orders.orderid,sum(orderinfo.qty*orderinfo.price) as order_total from orders
    -> inner join orderinfo
    -> on orders.orderid=orderinfo.orderid
    -> group by orderid;
+---------+-------------+
| orderid | order_total |
+---------+-------------+
|       1 |       79.90 | 
|       2 |       50.85 | 
|       3 |       34.95 | 
|       4 |      131.80 | 
+---------+-------------+
4 rows in set (0.00 sec)

this gives an incorrect value

这给出了一个不正确的值

mysql> select orders.orderid,sum(orderinfo.qty*orderinfo.price+orders.shipping) as order_total from orders
    -> inner join orderinfo
    -> on orders.orderid=orderinfo.orderid
    -> group by orderid;
+---------+-------------+
| orderid | order_total |
+---------+-------------+
|       1 |       89.88 | 
|       2 |       56.84 | 
|       3 |       38.94 | 
|       4 |      159.76 | 
+---------+-------------+
4 rows in set (0.00 sec)

1 个解决方案

#1

You are applying the shipping rate inside the SUM() aggregate, when really you need to add it afterward, to the result of the SUM(). This has the effect of adding the shipping value for each row of the aggregated group, that is, double shipping for orderid = 1 and 4x shipping for orderid = 4.

您正在将SUM()聚合中的运费应用到SUM()的结果中,之后真正需要将其添加到SUM()中。这具有为聚合组的每一行添加运费值的效果,即,orderid = 1的双重运送和orderid = 4的4x运送。

You can do this by adding shipping to the GROUP BY (actually MySQL doesn't require this, but it is good practice).

你可以通过向GROUP BY添加运输来实现这一点(实际上MySQL不需要这个,但这是一个好习惯)。

SELECT
  orders.orderid,
  /* Add `shipping` outside the SUM() aggregate */
  SUM(orderinfo.qty*orderinfo.price) + orders.shipping AS order_total 
FROM orders
  INNER JOIN orderinfo ON orders.orderid = orderinfo.orderid
GROUP BY
  orderid,
  shipping

Here's an example on SQLFiddle.com.