首页 > 代码库 > 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数据库)