解决方案 »
- oracle 多字段全完检索 怎么实现索引的自动更新
- oracle存储过程基础问题(在线等)
- oracle exp imp 字符集问题
- Oracle SQL Developer連接11G自帶的HR數據庫時報錯
- oracle如何跨用户和跨DB到处导入表的数据
- oracle快速恢复区flash_recovery_area里的controlxx.ctl内容为什么总是最新的?
- Navicat 8 for Oracle連數據庫時出現錯誤,Oracle版本是 8.1.7,什么原因
- 关于数据泵的二次恢复问题
- 高分求一些关于oracle的英汉对应的文章,教材,题目都可以
- 菜鸟急问!!
- oralce 插入中文乱码问题
- oracle是我dblink事务问题。
(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;