November 26, 2016 essay (about Oracle database)

Today wrote a few hours of SQL statements, at the beginning I did not train of thought, the idea began to write.

First I query the storage table in the 3-level units under the various outlets of the storage information, find this information, I went to the storage schedule to query the details of the storage

I found the individual IDs of the packing boxes I wanted.

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)

Find out more about inbound orders under each dot

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))

The damaged and intact
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;
The damaged and intact bundle
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;
Damaged and intact boxes
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 ');
Damaged and intact packages
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;

After I found the last data I wanted, I processed the data in my logic layer to get the results I wanted.

Find their own SQL write very scattered, are a piece of not combined, directly get the result set I want and then I began to conceive

Yungo advice to me is to start from the deepest source of data to find their own code, step-by-step move up, get the results you want, I also did the analysis

Finding this really improves my efficiency in database operations and gets the results I want faster.

November 26, 2016 essay (about Oracle database)