create table Collection --建立征收测试表
(TaxpayersCode Number(7),TaxesCode Number(2),TaxesName Varchar(20),Tax Number(7,2),TaxData date);
insert into Collection values(1000000,11,'营业税',10.22,to_date('20050101','yyyymmdd'));
insert into Collection values(1000001,11,'营业税',20.00,to_date('20050101','yyyymmdd'));
insert into Collection values(1000000,12,'个人所得税',1.22,to_date('20050101','yyyymmdd'));
insert into Collection values(1000001,12,'个人所得税',4.22,to_date('20050101','yyyymmdd'));
insert into Collection values(1000002,12,'个人所得税',9.22,to_date('20050101','yyyymmdd'));
insert into Collection values(1000000,13,'城建税',0.22,to_date('20050101','yyyymmdd'));
insert into Collection values(1000001,13,'城建税',0.52,to_date('20050101','yyyymmdd'));
insert into Collection values(1000001,14,'资源税',7.22,to_date('20050101','yyyymmdd'));
insert into Collection values(1000001,15,'企业所得税',18.12,to_date('20050101','yyyymmdd'));
insert into Collection values(1000000,15,'企业所得税',60.12,to_date('20050101','yyyymmdd'));
drop table Collection;--删除测试表---------------------------------------------------------------------------------------
以上是基本数据信息,实际内容条数非常多!
TAXPAYERSCODE TAXESCODE TAXESNAME TAXDATA SUM(TAX)
1 1000001 11 营业税 2005-1-1 20
2 1000000 12 个人所得税 2005-1-1 1.22
3 1000000 11 营业税 2005-1-1 10.22
4 1000001 12 个人所得税 2005-1-1 4.22
5 1000000 13 城建税 2005-1-1 0.22
6 1000001 13 城建税 2005-1-1 0.52
7 1000001 15 企业所得税 2005-1-1 18.12
8 1000000 15 企业所得税 2005-1-1 60.12
9 1000001 14 资源税 2005-1-1 7.22
10 1000001 12 个人所得税 2005-1-1 9.22
这是查询的结果
------------------------------------------------------------------------------------------------------
现在我要统计相同纳税人所交纳的所有税种,也就是说以TAXESNAME生成动态列!
TAXPAYERSCODE 营业税 个人所得税 城建税 资源税 企业所得税 征收日期 SUM(TAX)
1 1000001 20 4.22 0.52 7.22 18.12 2005-1-1 50.08
2 1000000 10.22 1.22 0.22 0 60.12 2005-1-1 71.78
3 1000002 0 9.22 0 0 0 2005-1-1 9.22
-----------------------------------------------------------------------------------------------------
我现在用的办法是静态的,代码如下:
select TaxpayersCode as "纳税人代码",
sum(case TaxesCode when 11 then Tax else 0 end) as "营业税",
sum(case TaxesCode when 12 then Tax else 0 end) as "个人所得税",
sum(case TaxesCode when 13 then Tax else 0 end) as "城建税",
sum(case TaxesCode when 14 then Tax else 0 end) as "资源税",
sum(case TaxesCode when 15 then Tax else 0 end) as "企业所得税",
TaxData as "征收日期",
sum(Tax)
from Collection
group by TaxpayersCode,TaxData;
------------------------------------------------------------------------------------------------------
但是这样达不到我的要求!因为税种会变动,有些人可能只要交一种税,或着某几种税
当我统计某一类人的时候!那么上面的代码,就会出现很多没用的值为0的列!
而且税种可能会增加,到时候再去改动程序非常麻烦!
所以问问高手没有没动态生成列的办法!还有一个限定条件,我写的程序是一个查询模块,属于子系统。只有查询权限。存储过程啥的都不能用!
希望高手能帮下!
(TaxpayersCode Number(7),TaxesCode Number(2),TaxesName Varchar(20),Tax Number(7,2),TaxData date);
insert into Collection values(1000000,11,'营业税',10.22,to_date('20050101','yyyymmdd'));
insert into Collection values(1000001,11,'营业税',20.00,to_date('20050101','yyyymmdd'));
insert into Collection values(1000000,12,'个人所得税',1.22,to_date('20050101','yyyymmdd'));
insert into Collection values(1000001,12,'个人所得税',4.22,to_date('20050101','yyyymmdd'));
insert into Collection values(1000002,12,'个人所得税',9.22,to_date('20050101','yyyymmdd'));
insert into Collection values(1000000,13,'城建税',0.22,to_date('20050101','yyyymmdd'));
insert into Collection values(1000001,13,'城建税',0.52,to_date('20050101','yyyymmdd'));
insert into Collection values(1000001,14,'资源税',7.22,to_date('20050101','yyyymmdd'));
insert into Collection values(1000001,15,'企业所得税',18.12,to_date('20050101','yyyymmdd'));
insert into Collection values(1000000,15,'企业所得税',60.12,to_date('20050101','yyyymmdd'));
drop table Collection;--删除测试表---------------------------------------------------------------------------------------
以上是基本数据信息,实际内容条数非常多!
TAXPAYERSCODE TAXESCODE TAXESNAME TAXDATA SUM(TAX)
1 1000001 11 营业税 2005-1-1 20
2 1000000 12 个人所得税 2005-1-1 1.22
3 1000000 11 营业税 2005-1-1 10.22
4 1000001 12 个人所得税 2005-1-1 4.22
5 1000000 13 城建税 2005-1-1 0.22
6 1000001 13 城建税 2005-1-1 0.52
7 1000001 15 企业所得税 2005-1-1 18.12
8 1000000 15 企业所得税 2005-1-1 60.12
9 1000001 14 资源税 2005-1-1 7.22
10 1000001 12 个人所得税 2005-1-1 9.22
这是查询的结果
------------------------------------------------------------------------------------------------------
现在我要统计相同纳税人所交纳的所有税种,也就是说以TAXESNAME生成动态列!
TAXPAYERSCODE 营业税 个人所得税 城建税 资源税 企业所得税 征收日期 SUM(TAX)
1 1000001 20 4.22 0.52 7.22 18.12 2005-1-1 50.08
2 1000000 10.22 1.22 0.22 0 60.12 2005-1-1 71.78
3 1000002 0 9.22 0 0 0 2005-1-1 9.22
-----------------------------------------------------------------------------------------------------
我现在用的办法是静态的,代码如下:
select TaxpayersCode as "纳税人代码",
sum(case TaxesCode when 11 then Tax else 0 end) as "营业税",
sum(case TaxesCode when 12 then Tax else 0 end) as "个人所得税",
sum(case TaxesCode when 13 then Tax else 0 end) as "城建税",
sum(case TaxesCode when 14 then Tax else 0 end) as "资源税",
sum(case TaxesCode when 15 then Tax else 0 end) as "企业所得税",
TaxData as "征收日期",
sum(Tax)
from Collection
group by TaxpayersCode,TaxData;
------------------------------------------------------------------------------------------------------
但是这样达不到我的要求!因为税种会变动,有些人可能只要交一种税,或着某几种税
当我统计某一类人的时候!那么上面的代码,就会出现很多没用的值为0的列!
而且税种可能会增加,到时候再去改动程序非常麻烦!
所以问问高手没有没动态生成列的办法!还有一个限定条件,我写的程序是一个查询模块,属于子系统。只有查询权限。存储过程啥的都不能用!
希望高手能帮下!
insert into A values('单位A','营业税',10.2)
insert into A values('单位B','营业税',20.2)
insert into A values('单位A','增值税',11.1)
insert into A values('单位B','增值税',22.3)
insert into A values('单位C','增值税',12.5)
insert into A values('单位C','印花税',21.5)
--测试
declare col1 a.税种%type; --定义类型
sqlstr varchar2(8000);
cursor c_try is select distinct 税种 from a;
begin
sqlstr:='';
open c_try;
loop
fetch c_try into col1;
exit when c_try%notfound;
sqlstr:=sqlstr||', sum(decode(税种,'''||col1||''',金额,0.0)) as '||col1;
exit when c_try%notfound;
end loop;
close c_try;
sqlstr:='create table tt as select 单位名称,sum(金额) as 合计'||sqlstr||' from a group by 单位名称';
--tt为刚创建的一个保存结果的表
execute immediate sqlstr;
end;--执行
select * from tt;
直接执行查询即可。
sqlstr varchar2(8000);
begin
sqlstr:='select 1,2 from temp$lob';
execute immediate sqlstr;
end;啥反应也没有!也没出错!
MSSQL中到是可以直接支持直接SELECT!但是OARCLE不行,要into给变量。
我不通过中间表应当如何得到查询结果。
你每次只要要调用这个存储过程,然后查询V_TEST 视图即可
所以不行!
我试过多种办法,但是直接用SQL语句转换,效率低下!
在数据量大的时候,响应很慢。
已经改用程序直接实现交叉表。