有两个表：
order -- 订单表，其中有四个字段：
id -- 订单号
out -- 出库(仓库id)
in -- 入库(仓库id)
count -- 订单中商品数量
warehouse -- 仓库表，其中两个字段：
id -- 仓库id
address -- 仓库地址
如何用一条语句，统计每个仓库所有出库和入库商品数量，出库为-，入库为+，并且按仓库名排序，例如：
仓库 仓库地址 数量变动 相关订单号
101 北京 -10 123498
101 北京 +30 230498
102 上海 +10 123498
102 上海 -30 230498
提示：MySQL中拼接字符串的函数为CONCAT(a, b)
select b.id, b.address, (case when a.out=b.id then 0-a.count else a.count end) count, a.id orderid from `order` a
left join warehouse b on a.out=b.id or a.in=b.id
order by b.id;
大概这样？
SELECT * FROM ((SELECT `out` AS `warehouse_id`,CONCAT('-',`count`) AS `count`,`id` AS `order_id` FROM `order`) UNION ALL (SELECT `in` AS `warehouse_id`,CONCAT('+',`count`) AS `count`,`id` AS `order_id` FROM `order`)) AS `temp` ORDER BY `warehouse_id`
分解开来就是先分别查出库的
SELECT `out` AS `warehouse_id`,CONCAT('-',`count`) AS `count`,`id` AS `order_id` FROM `order`
和入库的
SELECT `in` AS `warehouse_id`,CONCAT('+',`count`) AS `count`,`id` AS `order_id` FROM `order`
再union合并起来
select * from (
select a.id aid,a.address,CONCAT('-',b.count),b.id from waarehouse a
left join order b on a.id=b.out
union all
select a.id aid,a.address,CONCAT('+',b.count),b.id from waarehouse a
left join order b on a.id=b.in
)e order by e.aid
我跟上面的回答差不多