我写了一个代码比较长,逻辑不复杂,但是比较死的,楼主可以对应入座,把写死的地方对应到变量就可以了with test_tabel as (select 99 as "税种", 13 "金额" from dual union all select 14 as"税种", 113 "金额" from dual union all select 20 as "税种", 200 "金额" from dual)--创建你的实际表 select tt.b, sum(case when tt.a = 99 then test_tabel.金额 / 13 * tt.c when tt.a = 14 then test_tabel.金额 / 1.13 * tt.c when tt.a = 20 then test_tabel.金额--主要是在这里进行汇总,也就是你的第三步 end) a from (select 99 a, 10 b, 7 c from dual union select 99 a, 11 b, 3 c from dual union select 99 a, 12 b, 2 c from dual union select 99 a, 13 b, 1 c from dual union select 14 a, 10 b, 0.07 c from dual union select 14 a, 11 b, 0.03 c from dual union select 14 a, 12 b, 0.02 c from dual union select 14 a, 13 b, 0.01 c from dual union select 14 a, 14 b, 1 c from dual union select 20 a, 20 b, 1 c from dual) tt,--tt是临时表,按照逻辑的临时表,创建了一列对多列,从而得到结果为多列 test_tabel where tt.a = test_tabel.税种 group by tt.b order by tt.b
bo不熟,写了sql的实现方式。 从业务上费率拆分表肯定要通过,临时表、数组等一种实现。 with tb1 as ( --原始数据 select 99 tax_type, 13 tax_num from dual union all select 14 tax_type, 113 tax_num from dual union all select 20 tax_type, 200 tax_num from dual ), tb2 as ( --税率拆分表 select 99 tax_type, 10 tax_type2 , 7/13 tax_num_ratio from dual union all select 99 tax_type, 11 tax_type2 , 3/13 tax_num_ratio from dual union all select 99 tax_type, 12 tax_type2 , 2/13 tax_num_ratio from dual union all select 99 tax_type, 13 tax_type2 , 1/13 tax_num_ratio from dual union all select 14 tax_type, 10 tax_type2 , 7/113 tax_num_ratio from dual union all select 14 tax_type, 11 tax_type2 , 3/113 tax_num_ratio from dual union all select 14 tax_type, 12 tax_type2 , 2/113 tax_num_ratio from dual union all select 14 tax_type, 13 tax_type2 , 1/113 tax_num_ratio from dual union all select 14 tax_type, 14 tax_type2 , 1 tax_num_ratio from dual ), tb3 as ( select tax_type2 as tax_type, round(sum(tax_num*tax_num_ratio),2) as tax_num from tb1, tb2 where tb1.tax_type = tb2.tax_type group by tax_type2 ) select tax_type, tax_num from tb3 union all select tax_type, tax_num from tb1 where tb1.tax_type not in(select distinct tb2.tax_type from tb2) order by tax_type;
(select 99 as "税种", 13 "金额"
from dual
union all
select 14 as"税种", 113 "金额"
from dual
union all
select 20 as "税种", 200 "金额" from dual)--创建你的实际表
select tt.b,
sum(case
when tt.a = 99 then
test_tabel.金额 / 13 * tt.c
when tt.a = 14 then
test_tabel.金额 / 1.13 * tt.c
when tt.a = 20 then
test_tabel.金额--主要是在这里进行汇总,也就是你的第三步
end) a
from (select 99 a, 10 b, 7 c
from dual
union
select 99 a, 11 b, 3 c
from dual
union
select 99 a, 12 b, 2 c
from dual
union
select 99 a, 13 b, 1 c
from dual
union
select 14 a, 10 b, 0.07 c
from dual
union
select 14 a, 11 b, 0.03 c
from dual
union
select 14 a, 12 b, 0.02 c
from dual
union
select 14 a, 13 b, 0.01 c
from dual
union
select 14 a, 14 b, 1 c
from dual
union
select 20 a, 20 b, 1 c from dual) tt,--tt是临时表,按照逻辑的临时表,创建了一列对多列,从而得到结果为多列
test_tabel
where tt.a = test_tabel.税种
group by tt.b
order by tt.b
我这个是报表已经出来了 后期展示的时候的需求,这个时候用不到sql查询了吧?
可以直接定义变量来解决吗?
BTW:能够通过定义变量来实现吗?
从业务上费率拆分表肯定要通过,临时表、数组等一种实现。
with
tb1 as ( --原始数据
select 99 tax_type, 13 tax_num from dual union all
select 14 tax_type, 113 tax_num from dual union all
select 20 tax_type, 200 tax_num from dual
),
tb2 as ( --税率拆分表
select 99 tax_type, 10 tax_type2 , 7/13 tax_num_ratio from dual union all
select 99 tax_type, 11 tax_type2 , 3/13 tax_num_ratio from dual union all
select 99 tax_type, 12 tax_type2 , 2/13 tax_num_ratio from dual union all
select 99 tax_type, 13 tax_type2 , 1/13 tax_num_ratio from dual union all
select 14 tax_type, 10 tax_type2 , 7/113 tax_num_ratio from dual union all
select 14 tax_type, 11 tax_type2 , 3/113 tax_num_ratio from dual union all
select 14 tax_type, 12 tax_type2 , 2/113 tax_num_ratio from dual union all
select 14 tax_type, 13 tax_type2 , 1/113 tax_num_ratio from dual union all
select 14 tax_type, 14 tax_type2 , 1 tax_num_ratio from dual
),
tb3 as (
select tax_type2 as tax_type, round(sum(tax_num*tax_num_ratio),2) as tax_num
from tb1, tb2 where tb1.tax_type = tb2.tax_type
group by tax_type2
)
select tax_type, tax_num from tb3
union all
select tax_type, tax_num from tb1
where tb1.tax_type not in(select distinct tb2.tax_type from tb2)
order by tax_type;