查看数据库表之间问题SQL

select * from (
select t1.id,t1.product_name_cd,t1.batch_num,t1.weight as w1,t2.weight as w2,IFNULL(t1.weight,0)-IFNULL(t2.weight,0) as diff from 
(
select id,product_name_cd,batch_num, sum( weight )  as weight  from in_out_stock_detail group by id, product_name_cd,batch_num
) t1
left join 
(
select detail_id,product_name_cd,batch_num, sum( weight)  as weight  from stock_place group by detail_id, product_name_cd,batch_num
) t2 on t1.id = t2.detail_id and t1.product_name_cd = t2.product_name_cd and t1.batch_num = t2.batch_num
) t where t.diff!=0;




-- 查看库存是否一致：库存表、库存调表、货位履历表
select t1.product_name_cd,t1.batch_num,t1.weight weight1,t2.weight weight2 ,t3.weight weight3 ,  t1.weight-IFNULL(t2.weight,0)-IFNULL(t3.weight,0) as diff from


(select product_name_cd,batch_num, sum(weight) as weight from warehouse.stock group by product_name_cd,batch_num) t1
left join 


(select product_name_cd,batch_num,sum( case when adjustment_type = 1 then weight*-1 else weight end ) as weight from warehouse.stock_adjustment group by product_name_cd,batch_num ) t2
on t1.product_name_cd = t2.product_name_cd and t1.batch_num = t2.batch_num
left join 


(select product_name_cd,batch_num,sum( case when inoutsock_type = 0 then weight*-1 else weight end ) as weight from warehouse.stock_place group by product_name_cd,batch_num ) t3
on t1.product_name_cd = t3.product_name_cd and t1.batch_num = t3.batch_num


where t1.weight-IFNULL(t2.weight,0)-IFNULL(t3.weight,0)  > 0;




-- 库存表和货位表的重量差
select t1.goodsplace_cd,t1.weight weight1, t4.weight weight4 ,  t1.weight - IFNULL(t4.weight,0) as diff from


(select goodsplace_cd, sum(weight) as weight from warehouse.stock group by goodsplace_cd) t1
left join 


(select goodsplace_cd,sum( weight) as weight from warehouse.goodsplace group by goodsplace_cd ) t4
on t1.goodsplace_cd = t4.goodsplace_cd


where
t1.weight - IFNULL(t4.weight,0) > 0


order by replace(t1.goodsplace_cd, '-', '')+0;


select (select sum(weight) from stock) - (select sum(weight) from goodsplace);




-- 查看 出入库中已执行，货位履历中  未执行的数据
SELECT 
    ios.*
FROM
    in_out_stock ios
    INNER JOIN in_out_stock_detail iod ON ios.id = iod.in_out_stock_id
    INNER JOIN stock_place sp ON iod.id = sp.detail_id
WHERE
    sp.execute_status = '0'
AND ios.inoutsock_confirm = 1
;