我想从两个表求和，然后计算两个值以获得收入

vnzz0bqm 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(258)

我想把费用表中的（费用金额）和订单表中的（订单金额）按日期相加，然后计算两者的值以获得收入

Table 'Expenses'
Fields: expense_amount | expense_date
Data:   1000 | 2018-11-17
        1500 | 2018-11-17
        22 | 2018-11-18

Table 'Order'
Fields: order_amount | order_date
Data:   25000 | 2018-11-17
        10000 | 2018-11-17
        8000  | 2018-11-18

我只想按日期合计（费用金额）和（订单金额），然后减去这两个值就可以得到收入
对于单表，它使用以下代码

<?php 
  if(isset($_POST["date"]))
  {
  if($_POST["date"] != '')
  {
    $query = "SELECT sum(sub_total) AS total_order_value FROM tbl_order     
    tbl_order.order_customer_id
    WHERE order_date = '".$_POST["date"]."'";
   }
  else
  {
    $query = "SELECT sum(sub_total) AS total_order_value FROM tbl_order";
  }
  $result = mysqli_query($conn, $query);

  while($row = mysqli_fetch_array($result))
  {
    echo "Rs. ";
    echo number_format($row['total_order_value'],2);
  }

  }

?>

sql mysql php

来源：https://stackoverflow.com/questions/53352638/i-want-to-sum-from-two-tables-then-substrate-both-the-value-to-get-revenue

2条答案

按热度按时间

gfttwv5a1#

我建议 union all 以及 group by :

select dte, sum(expense_amount) as expense_amount,
       sum(order_amount) as order_amount,
       sum(expense_amount) - sum(order_amount) as net
from ((select expense_date as dte, expense_amount, 0 as order_amount
       from expenses
      ) union all
      (select order_date as dte, 0 as expense_amount, order_amount
       from expenses
      ) 
     ) oe
group by dte;

赞(0）回复(0）举报 2021-06-19

busg9geu2#

一种方法是使用两个独立子查询的连接，通过按日期聚合分别查找订单和费用的总和。然后，对于每个日期，我们可以找到收入。

SELECT
    t1.order_date,
    t1.order_sum - t2.expense_sum AS revenue
FROM
(
    SELECT order_date, SUM(order_amount) AS order_sum
    FROM orders
    GROUP BY order_date
) t1
INNER JOIN
(
    SELECT expense_date, SUM(expense_amount) AS expense_sum
    FROM expenses
    GROUP BY expense_date
) t2
    ON t1.order_date = t2.expense_date;

这个答案假设给定的日期总是有一个订单和费用金额。

赞(0）回复(0）举报 2021-06-19

我来回答

我想从两个表求和，然后计算两个值以获得收入

2条答案

相关问题

热门标签

最新问答