首页 > 代码库 > 2016年11月26号随笔(关于oracle数据库)

2016年11月26号随笔(关于oracle数据库)

今天写了几个小时的sql语句,一开始我并没有思路,有思路便开始写。

 

首先我查询了入库表中的3级单位下的各个网点的入库信息,找到这些信息后,我又去入库明细表中查询入库的详细信息

找到了我要的把捆包箱的各个id

select * from gzh_instock where outunitid
in (select organizationid from cdms_organization start with
organizationid=‘3862fa81-ac03-44de-8e82-d39dacce9c9d‘
connect by prior ORGANIZATIONID = PARENTID)

//查询各个网点下的入库单的详细信息

select * from gzh_instock_detail where billid
in (select billid from gzh_instock where outunitid
in (select organizationid from cdms_organization start with
organizationid=‘3862fa81-ac03-44de-8e82-d39dacce9c9d‘
connect by prior ORGANIZATIONID = PARENTID))


//把的残损和完整
select ISDAMAGE,count(ISDAMAGE) from gzh_bainfo where barcode
in (select barcode from gzh_instock_detail where billid
in (select billid from gzh_instock where outunitid
in (select organizationid from cdms_organization start with
organizationid=‘3862fa81-ac03-44de-8e82-d39dacce9c9d‘
connect by prior ORGANIZATIONID = PARENTID))and to_char(billdate,‘yyyy-MM‘)=‘2016-10‘ and UNIT=‘1‘) group by ISDAMAGE;
//捆的残损和完整
select ISDAMAGE,count(ISDAMAGE) from gzh_BunchInfo where bunchcode
in (select barcode from gzh_instock_detail where billid
in (select billid from gzh_instock where outunitid
in (select organizationid from cdms_organization start with
organizationid=‘3862fa81-ac03-44de-8e82-d39dacce9c9d‘
connect by prior ORGANIZATIONID = PARENTID)) and UNIT=‘2‘) group by ISDAMAGE;
//箱的残损和完整
select intactnum,damagenum from gzh_boxinfo where devcode
in (select barcode from gzh_instock_detail where billid
in (select billid from gzh_instock where outunitid
in (select organizationid from cdms_organization start with
organizationid=‘3862fa81-ac03-44de-8e82-d39dacce9c9d‘
connect by prior ORGANIZATIONID = PARENTID)) and UNIT=‘3‘);
//包的残损和完整
select intactnum,damagenum from gzh_pachetinfo where rfidno
in (select barcode from gzh_instock_detail where billid
in (select billid from gzh_instock where outunitid
in (select organizationid from cdms_organization start with
organizationid=‘3862fa81-ac03-44de-8e82-d39dacce9c9d‘
connect by prior ORGANIZATIONID = PARENTID)) and UNIT=‘4‘);
select * from gzh_boxinfo;

 

找到最后我要的数据后,我在我的逻辑层进行了数据的处理,得到我想要的结果。

发现自己的sql写的很散,都是一块一块的没有结合起来,直接得到我要的结果集然后我开始构思

尹哥给我的建议是从最深处的数据源开始找自己想要的代码,一步一步 的上移,得到自己想要的结果,我也是做了分析

发现这个确实提高了我对数据库操作的效率,更快的得到了自己想要的结果。

2016年11月26号随笔(关于oracle数据库)