mysql从另一个select查询值中减去一个select查询

hxzsmxv2 于 2021-06-20 发布在 Mysql

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

我有两个mysql表，我必须使用这两个表构建select查询，两个查询输出都是来自inventory\u transfer\u details表的数量的总和。我需要从“total\ u from”中减去“total\ u to”。请检查以下是mysql表。
库存转移

库存转移明细

下面是我的两个问题。
第一个查询：

select sum(b.transfer_quantity) as total_to 
from inventory_transfers as a 
join inventory_transfer_details as b on a.id = b.inventory_transfer_id 
where a.status="approved" and b.inventory_or_composite_id = '1' and a.to_warehouse_id = '2'

第二个查询：

select sum(b.transfer_quantity) as total_from 
from inventory_transfers as a 
join inventory_transfer_details as b on a.id = b.inventory_transfer_id 
where a.status="approved" and b.inventory_or_composite_id = '1' and a.from_warehouse_id = '2'

我需要从查询中减去转移到转移
你有什么指南可以把这个最终的查询转换成laravel查询吗？

mysql php laravel

来源：https://stackoverflow.com/questions/52629777/mysql-subtract-one-select-query-from-another-select-query-value

2条答案

按热度按时间

vuv7lop31#

我想为这个问题提供一个更普遍的答案：减去任何 SUM() 从任何其他 SUM() . 你可以简单的做。。。

SELECT Total1, Total2, Total1 - Total2
FROM
    (SELECT SUM(id) Total1 FROM TableA) AS a
INNER JOIN
    (SELECT SUM(id) Total2 FROM TableB) AS b;

在你的例子中，两个子查询， a 以及 b ，将替换为当前用于计算和的两个查询。

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

4uqofj5v2#

您可以组合这些查询并获得差异

SELECT
  sum(
    IF(a.to_warehouse_id = '2', b.transfer_quantity, 0)
  ) - sum(
    IF(a.from_warehouse_id = '2', b.transfer_quantity, 0)
  ) as total
FROM
  inventory_transfers AS a
  JOIN inventory_transfer_details AS b ON a.id = b.inventory_transfer_id
WHERE
  a.status = "approved"
  AND b.inventory_or_composite_id = '1'

在laravel查询生成器中，此查询可能类似于

DB::table('inventory_transfers as a')
    ->select(DB::raw('sum(IF(a.to_warehouse_id = '2', b.transfer_quantity, 0)) - sum(IF(a.from_warehouse_id = '2', b.transfer_quantity, 0)) as total'))
    ->join('inventory_transfer_details as b', DB::raw('a.id'), '=', DB::raw('b.inventory_transfer_id'))
    ->where([
        ['a.status', '=', 'approved']
        ['b.inventory_or_composite_id', '=', 1]
    ])
    ->get()

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

我来回答

mysql从另一个select查询值中减去一个select查询

2条答案

相关问题

热门标签

最新问答