Query statements in Oracle (about the outbound inbound information table, schedule, the Bundle box table, the Unit information table of the centralized query)

--Find out the unit ID of all cash centers
With
Allunit
As
Select T.organizationid Orgid,t.parentid
From Cdms_organization t where T.category = 4
Start with T.organizationid = ' 05e85693-14b0-4582-8063-8fbde85371f0 '
Connect by T.parentid = PRIOR T.organizationid),

N_instock
As
--instock full money, damaged money, field: Inbound Unit Id,mone1,money2
Select Inunitid, (Baint+bunint+intaintnum+baoint) Intmoney, (BADMA+BUNDMA+BAODMA) Dmamoney
From
Select TBA. Inunitid,
NVL (case Ba. Isdamage when 0 then 100*ba.valuta end), 0) as Baint,
NVL (case Ba. Isdamage when 1 then 100*ba.valuta end), 0) as BADMA,
NVL (case bun. Isdamage when 0 then 1000*bun.valuta end), 0) as Bunint,
NVL (case bun. Isdamage when 1 then 1000*bun.valuta end), 0) as BUNDMA,
NVL (INTA). Counter*inta. valuta,0) INTAINTNUM,NVL (pack. intactnum,0) Baoint,
NVL (Pack. damagenum,0) Baodma from
(
Select It.inunitid,de.barcode,de. UNIT from Gzh_instock it
Left joins Gzh_instock_detail de on it. Billid=de. billID
--where to_char (it.billdate, ' yyyy-mm ') = ' 2016-11 ' and intype= ' 1311 '
) TBA
Left joins Gzh_bainfo BA on TBA. Barcode=ba. BARCODE and Tba.unit=1
Left joins Gzh_bunchinfo Bun on TBA. Barcode=bun. Bunchcode and tba.unit=2
Left join Gzh_pachetinfo pack on Tba.barcode=pack. Rfidno and Tba.unit=4
Left joins Gzh_intactboxinfo Inta on TBA. Barcode=inta. BARCODE and Tba.unit=3
)
)
,
AllData
As
Select T.PARENTID,NVL (SUM (T1). Flittingmoney), 0) Outmoney,
NVL (SUM (T2). Intmoney), 0) Intmoney,
NVL (SUM (T2). Dmamoney), 0) Dmamoney from Allunit t
Left join Gzh_outstock T1 on T.orgid=t1.outunitid
Left join N_instock T2 on T.orgid=t2.inunitid
--where to_char (t1.billdate, ' yyyy-mm ') = ' 2016-11 ' and outtype= ' 1321 '
GROUP BY T.parentid
)
,
Tfinfo
As
(
Select ParentID,
Decode ((select SUM (Outmoney) from AllData), 0,0,outmoney/(select sum (Outmoney) from AllData)) Tfscale,
Decode ((select SUM (Intmoney) from AllData), 0,0,intmoney/(select sum (Intmoney) from AllData)) Hlwscale,
Decode ((select SUM (Dmamoney) from AllData), 0,0,dmamoney/(select sum (Dmamoney) from alldata)) Hlcscale
From AllData)

Select Fullname,tfscale ratio, Hlwscale full ratio, hlcscale full proportion
From Tfinfo left join Cdms_organization
On Cdms_organization.organizationid=tfinfo.parentid;

The above SQL is Yungo taught me, although not their own writing, but according to this idea, I found that I was right, but not the understanding of Oracle is not enough to write

Today, the main thing is to let me re-understand the Oracle query, and I learned before the SQL query gap is quite large, a variety of structural choices and a variety of code to write

In calculating the scale, we first sum the data, in order to not appear in the table empty characters, we chose the NVL (field name, 0), this is the empty string into O statement.

Today, I also used a very magical method, decode (a,0,0,b/a*100) This is to not let the divisor of 0, so that Oracle error, this let my statement finished writing faster.

I have recorded these two usages so that I can better remember the usage of these two words.

Query statements in Oracle (about the outbound inbound information table, schedule, the Bundle box table, the Unit information table of the centralized query)