数据库的一些查询

来源：互联网发布：js判断对象属于哪个类编辑：程序博客网时间：2024/05/16 12:36

（1）查询库存表某商品的库存

select t.*, t.rowid from stock_count t where t.itm_myid = '000000-0000025029'

（2）查询库存表某商品的库存系统库存，拣货库存，分开各个储位。

select t.cache_qty,t.pic_cache_qty,t.itm_qty,t.itm_myid, t.rowid from stock_count t where t.itm_myid in ('000044-0000017990',
'000149-0000011358',
'000165-0000015011',
'000635-0000116688',
'000635-0000116790')

（3）查询库存表某商品的库存系统库存，拣货库存，求和。

select t.itm_myid,it.itm_name,l.loc_myid,t.stk_type,sum(t.cache_qty),sum(t.itm_qty),sum(t.pic_cache_qty) from stock_count t,location l,item_txt it where t.itm_myid in ('001938-0000146735',
'001938-0000146733',
'001938-0000146734',
'003151-0000212060
') and t.loc_id = l.loc_id and t.itm_id = it.itm_id group by t.itm_myid,it.itm_name,l.loc_myid,t.stk_type;

（4）查询库存表某商品的库存系统库存，拣货库存，储位。

select t.itm_myid,it.itm_name,l.loc_myid,t.stk_type,sum(t.cache_qty),sum(t.itm_qty),sum(t.pic_cache_qty),t.rowid from stock_count t,location l,item_txt it where t.itm_myid in ('001938-0000146735',
'000026-0000001616',
'001938-0000146732',
'001938-0000146733',
'001938-0000146734',
'003151-0000212060
') and t.loc_id = l.loc_id and t.itm_id = it.itm_id group by t.itm_myid,it.itm_name,l.loc_myid,t.stk_type,t.rowid;

（5）查询订单的商品的装箱，上架，验收数量

select 装箱,上架,验收,crr.itm_myid,cpr.stk_type from
(select sum(cp.pkg_qty*cp.box_qty) as 装箱,sum(cp.box_qty*(cp.pkg_qty - cp.no_st_pkg)) as 上架,cp.itm_myid,cp.stk_type
from check_packing cp where cp.po =
(select distinct cr.wmsord_id from check_record cr where cr.storage_myid = 'WT201108250001')
group by cp.itm_myid,cp.stk_type
order by cp.itm_myid ) cpr left join

(select sum(t.normal_qty)+sum(t.flaw_qty) as 验收,t.itm_myid from check_record t
where t.storage_myid = 'WT201108250001' group by t.itm_myid order by t.itm_myid) crr on cpr.itm_myid = crr.itm_myidselect 装箱,上架,验收,crr.itm_myid,cpr.stk_type from
(select sum(cp.pkg_qty*cp.box_qty) as 装箱,sum(cp.box_qty*(cp.pkg_qty - cp.no_st_pkg)) as 上架,cp.itm_myid,cp.stk_type
from check_packing cp where cp.po =
(select distinct cr.wmsord_id from check_record cr where cr.storage_myid = 'WT201108250001')
group by cp.itm_myid,cp.stk_type
order by cp.itm_myid ) cpr left join

(select sum(t.normal_qty)+sum(t.flaw_qty) as 验收,t.itm_myid from check_record t
where t.storage_myid = 'WT201108250001' group by t.itm_myid order by t.itm_myid) crr on cpr.itm_myid = crr.itm_myid

（6）查询批次里面的配送公司的订单

select wn.deliver_comp,t.created,t.wmsord_myid,t.pc from wmsorder_sheet t left join wmsord_notice wn on t.wmsord_id = wn.wmsord_id
where wn.deliver_comp='元智捷诚' and t.pc='2011-08-18-1'