Sql statements for help, how to calculate the same field and assigned to the fields of peers-CodePudding

The last column of the data the way how to calculate and has the union on the bottom of the current, the first two columns have been solved

 select tA. *, 
TA. ZS + tA. SY + tA, TP + ta.621 SX + tA. QT HJ from (select JG as FGS, 'state of current time employees' as persontype, 
The sum (case when ZTBM=001 then 1 else 0 end) as ZS, 
The sum (case when ZTBM=002 then 1 else 0 end) as SY, 
The sum (case when ZTBM=014 then 1 else 0 end) as TP, 
The sum (case when ZTBM=015 then 1 else 0 end) as SX, 
The sum (case when ZTBM not in (001002014015), then one else 0 end) as QT 
The from (
Select t1 fname_l2 XM, t1. Fnumber GH, t2. Fnumber JGBM, t2. Fname_l2 JG, t3. Fname_l2 ZT, t3. Fnumber ZTBM, 
To_char (t4) fenterdate, 'YYYY - MM - DD) RZRQ 
The from T_BD_Person t1 left join 
T_hr_personposition t4 on t1. Fid=t4. Fpersonid left join 
T_ORG_Admin t2 on t1. Fhrorgunitid=t2. Fid left join 
T_HR_BDEmployeeType t3 on t1. Femployeetypeid=t3. Fid) where instr (JG, 'branch) & gt; 1 or JG in (' business headquarters', 'operation support center') 
Group by JG 

Union all 

The select JG as FGS, 'state of specified time employees' as persontype, sum (case when ZTBM=001 then 1 else 0 end) as ZS, 
The sum (case when ZTBM=002 then 1 else 0 end) as SY, 
The sum (case when ZTBM=014 then 1 else 0 end) as TP, 
The sum (case when ZTBM=015 then 1 else 0 end) as SX, 
The sum (case when ZTBM not in (001002014015), then one else 0 end) as QT 
The from (
Select t1 fname_l2 XM, t1. Fnumber GH, t2. Fnumber JGBM, t2. Fname_l2 JG, t3. Fname_l2 ZT, t3. Fnumber ZTBM, 
To_char (t4) fenterdate, 'YYYY - MM - DD) RZRQ 
The from T_BD_Person t1 left join 
T_hr_personposition t4 on t1. Fid=t4. Fpersonid left join 
T_ORG_Admin t2 on t1. Fhrorgunitid=t2. Fid left join 
T_HR_BDEmployeeType t3 on t1. Femployeetypeid=t3. Fid where to_char (t4) fenterdate, '- DD YYYY - MM) & lt; The parameter (' querydate)) where instr (JG, 'branch) & gt; 1 or JG in (' business headquarters', 'operation support center') 
Group by JG) tA where ta.621 FGS in (parameter (' organization '))

Applications with a company's statements, the execution result is

CodePudding user response:

Now require () - specifies that month month/month of data specified as the value of the second column, add in the current down to the bottom of the query results, using tools to do it again crosstab can, advice on what to do,

This requirement is simpler, please click the following format simple column under your need,
1. The known data are as follows:
,,,,,,

2. That achieve the following effects:
,,,,,,

CodePudding user response:

According to your SQL, I can't write the code can only offer you train of thought: the enclosed calculation data during the month of SQL, the individual name a; Enclosed data computing time specified SQL, individual name b; A left join b on gs=a.f b. gs; From this join FGS, persontype, (a.z s - b.z s)/b.z s as zs, and so on, at the back of the calculated results unioin after all