(select the '2017-3-1 s' yf, b., a. *, case ZBSL when 0 then' ERROR 'else to_char (round ((ZBSL - FBSL)/ZBSL * 100, 2),' fm999999990.999999999 ') | | '%' end CXCL from
ZBL (select count (distinct a.h h), sum (b.s CSL) ZBSL from da_yhbk a, cb_slb_ls b, da_glszb c
Where a.h h=c. h and a.h h=b.h h and b.s fyf between add_months (the date '2017-3-1', 2) and the date '2017-3-1'
And c.q ybm='QQQQ'
) a,
FBL (select count (distinct a.h h), sum (b.s CSL) FBSL from da_yhbk a, cb_slb_ls b
Where slqy='QQQQ and a.h h=b.h h and b.s fyf between add_months (the date' 2017-3-1 ', 2) and the date '2017-3-1'
B)
Where 1=1)
Union all
(select the '2017-6-1 s' yf, b., a. *, case ZBSL when 0 then' ERROR 'else to_char (round ((ZBSL - FBSL)/ZBSL * 100, 2),' fm999999990.999999999 ') | | '%' end CXCL from
ZBL (select count (distinct a.h h), sum (b.s CSL) ZBSL from da_yhbk a, cb_slb_ls b, da_glszb c
Where a.h h=c. h and a.h h=b.h h and b.s fyf between add_months (the date '2017-6-1', 2) and the date '2017-6-1'
And c.q ybm='QQQQ'
) a,
FBL (select count (distinct a.h h), sum (b.s CSL) FBSL from da_yhbk a, cb_slb_ls b
Where slqy='QQQQ and a.h h=b.h h and b.s fyf between add_months (the date' 2017-6-1 ', 2) and the date '2017-6-1'
B)
Where 1=1)
Union all
(select the '2017-9-1 s' yf, b., a. *, case ZBSL when 0 then' ERROR 'else to_char (round ((ZBSL - FBSL)/ZBSL * 100, 2),' fm999999990.999999999 ') | | '%' end CXCL from
ZBL (select count (distinct a.h h), sum (b.s CSL) ZBSL from da_yhbk a, cb_slb_ls b, da_glszb c
Where a.h h=c. h and a.h h=b.h h and b.s fyf between add_months (the date '2017-9-1', 2) and the date '2017-9-1'
And c.q ybm='QQQQ'
) a,
FBL (select count (distinct a.h h), sum (b.s CSL) FBSL from da_yhbk a, cb_slb_ls b
Where slqy='QQQQ and a.h h=b.h h and b.s fyf between add_months (the date' 2017-9-1 ', 2) and the date '2017-9-1'
B)
Where 1=1)
Union all
(select the '2017-12-1 s' yf, b., a. *, case ZBSL when 0 then' ERROR 'else to_char (round ((ZBSL - FBSL)/ZBSL * 100, 2),' fm999999990.999999999 ') | | '%' end CXCL from
ZBL (select count (distinct a.h h), sum (b.s CSL) ZBSL from da_yhbk a, cb_slb_ls b, da_glszb c
Where a.h h=c. h and a.h h=b.h h and b.s fyf between add_months (the date '2017-12-1', 2) and the date '2017-12-1'
And c.q ybm='QQQQ'
) a,
FBL (select count (distinct a.h h), sum (b.s CSL) FBSL from da_yhbk a, cb_slb_ls b
Where slqy='QQQQ and a.h h=b.h h and b.s fyf between add_months (the date' 2017-12-1 ', 2) and the date '2017-12-1'
B)
Where 1=1)
CodePudding user response:
select yf, b. a. *, *, case ZBSL when 0 then 'ERROR' else to_char (round ((ZBSL - FBSL)/ZBSL * 100, 2), 'fm999999990.999999999') | | '%' end CXCL from
(select case when b.s fyf between add_months (the date '2017-3-1', 2) and the date '2017-3-1' then '2017-3-1'
The when b.s fyf between add_months (the date '2017-6-1', 2) and the date '2017-6-1' then '2017-6-1'
The when b.s fyf between add_months (the date '2017-9-1', 2) and the date '2017-9-1' then '2017-9-1'
The when b.s fyf between add_months (the date '2017-12-1', 2) and the date '2017-12-1' then '2017-12-1'
End yf,
ZBL count (distinct a.h h), sum (b.s CSL) ZBSL from da_yhbk a, cb_slb_ls b, da_glszb c
Where a.h h=c. h and a.h h=b.h h and b.s fyf between add_months (the date '2017-3-1', 2) and the date '2017-3-1'
And c.q ybm='QQQQ'
Group by case when b.s fyf between add_months (the date '2017-3-1', 2) and the date '2017-3-1' then '2017-3-1'
The when b.s fyf between add_months (the date '2017-6-1', 2) and the date '2017-6-1' then '2017-6-1'
The when b.s fyf between add_months (the date '2017-9-1', 2) and the date '2017-9-1' then '2017-9-1'
The when b.s fyf between add_months (the date '2017-12-1', 2) and the date '2017-12-1' then '2017-12-1'
End
) a,
(select case when b.s fyf between add_months (the date '2017-3-1', 2) and the date '2017-3-1' then '2017-3-1'
The when b.s fyf between add_months (the date '2017-6-1', 2) and the date '2017-6-1' then '2017-6-1'
The when b.s fyf between add_months (the date '2017-9-1', 2) and the date '2017-9-1' then '2017-9-1'
The when b.s fyf between add_months (the date '2017-12-1', 2) and the date '2017-12-1' then '2017-12-1'
End yf,
FBL count (distinct a.h h), sum (b.s CSL) FBSL from da_yhbk a, cb_slb_ls b
Where slqy='QQQQ and a.h h=b.h h and b.s fyf between add_months (the date' 2017-3-1 ', 2) and the date '2017-3-1'
Group by case when b.s fyf between add_months (the date '2017-3-1', 2) and the date '2017-3-1' then '2017-3-1'
The when b.s fyf between add_months (the date '2017-6-1', 2) and the date '2017-6-1' then '2017-6-1'
The when b.s fyf between add_months (the date '2017-9-1', 2) and the date '2017-9-1' then '2017-9-1'
The when b.s fyf between add_months (the date '2017-12-1', 2) and the date '2017-12-1' then '2017-12-1'
End
B)
Where 1=1 and a.y f=b.y f;
Should also can be optimized, the first you look at it, at least statement readability, reduce the number of scanning the tables
CodePudding user response:
And b.s fyf between add_months (the date '2017-3-1', 2) and the date '2017-3-1' a bit of a problem, remove the behind where the and b.s fyf between add_months (the date '2017-3-1', 2) and the date '2017-3-1'nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull