Material Statistics SQL

Itpub encountered a help to write SQL post, feel very interesting, so write out to see, the requirements are as follows:

There is a Schedule 1, the annual plan of the recording material has a practical use of table 2, recording the actual use of the situation. Finally, a statistical table, the plan and the actual data together to carry out statistics, there is a special situation is likely to be part of the material plan but no actual use, and some materials without planning but there is actual use of the situation, to merge these data together in accordance with the quarterly statistics and calculate the difference amount, can be achieved??

So the table interpolation data, the table structure is as follows:

CREATE table plan (obj varchar2, year number (4), plan_count number), create table actual (obj varchar2 Number (4), SEASON number (ten), used number (10));

The statements are as follows:

with T1 as (select distinct a.obj,p.plan_count,a.year,case when A.season=1 and sum (a.used) over (partition by A.obj,a.sea  son) Else null end as s1,case when a.season=2 and sum (a.used) over (partition by A.obj,a.season) Else null end as S2,case When A.season=3 and SUM (a.used) over (partition by A.obj,a.season) Else null end as s3,case when a.season=4 then sum (a.u  SED) over (partition by A.obj,a.season) Else null end as S4,sum (a.used) over (partition by a.obj) as Sum_count from actual A LEFT JOIN plan P in a.obj=p.obj Order by 1,4) Select TEMP.OBJ,NVL (temp.plan_count,0), TEMP.YEAR,TEMP.Q1,TEMP.Q2,TEMP.Q3, TEMP.Q4,NVL (temp.sum_count,0), (NVL (temp.plan_count,0)-NVL (temp.sum_count,0)) as diff from (select distinct T1.obj, T1.plan_count,t1.year,sum (S1) over (partition by T1.obj) as q1,sum (S2) through (partition by T1.obj) as Q2,sum (S3) over (part Ition by T1.obj) as Q3,sum (S4) through (partition by T1.obj) as Q4,t1.sum_count from T1union allselect pl.obj,pl.plan_count,p L.year,null,null,null,null,null FROm Plan PL where pl.obj not in (select distinct obj from actual)) Temp ORDER by 1 

The use of Nvl,case when,not in,over Partition,sum,union,left Join,with As and other writing, perennial write SQL should have more efficient wording, to be found after the supplement to improve.

Material Statistics SQL