需要说明的是:表:tb_lvy_levydata 又将近3000万条数据
第一个语句:
Select Rownum a ,
taxregcode b ,
taxpayername c ,
taxpayertypename d ,
taxpayerid e ,
gbtradename f ,
jurpname g ,
operaddress h ,
opertel i,
taxfinal1 j ,
taxfinal2 k ,
taxtypename2 l,
orgdeptname m,
taxofficialname n
From (Select Rownum,
a.taxregcode,
a.taxpayername,
c.taxpayertypename,
a.taxpayerid,
d.gbtradename,
a.jurpname,
b.operaddress,
b.opertel,
trim(to_char(nvl(tt1.taxfinal1,0),'999999999990.99')) taxfinal1,
trim(to_char(tt2.taxfinal2,'999999999990.99')) taxfinal2,
taxtypename2,
g.orgdeptname,
h.taxofficialname
From tb_reg_taxpayerbasicinfo a,
tb_reg_taxpayerextinfo b,
tc_reg_taxpayertype c,
tc_pub_gbtrade d,
tc_pub_taxorgdept g,
tc_pub_taxofficialspec h,
(Select t.taxregcode taxregcode1,e.taxtypename taxtypename1,sum(t.taxfinal) taxfinal1
From tb_lvy_levydata t,tc_pub_taxtype e
Where t.negativeflag='0'
And t.taxfinal>0
And ((t.paymode='02' And t.enterfisctime Is Null)
Or (t.paymode='01' And t.comparetime Is Null ))
And t.taxregcode Not Like 'T%'
And to_char(t.paymentterm,'yyyy')<'2008'
And t.taxtypecode=e.taxtypecode(+)
Group By taxregcode,e.taxtypename) tt1,
(Select t.taxregcode taxregcode2,e.taxtypename taxtypename2,t.taxtypecode taxtypecode,sum(t.taxfinal) taxfinal2
From tb_lvy_levydata t,tc_pub_taxtype e
Where t.negativeflag='0'
And t.taxfinal>0
And ((t.paymode='02' And t.enterfisctime Is Null)
Or (t.paymode='01' And t.comparetime Is Null ))
And t.taxregcode Not Like 'T%'
And to_char(t.paymentterm,'yyyymm')<='2008'||'01'
And t.taxtypecode=e.taxtypecode(+)
Group By taxregcode,e.taxtypename,t.taxtypecode) tt2
Where a.taxregcode=tt2.taxregcode2
And tt1.taxregcode1(+)=tt2.taxregcode2
And tt1.taxtypename1(+)=tt2.taxtypename2
And a.taxregcode=b.taxregcode
And b.taxregcode=tt2.taxregcode2
And a.taxpayertypecode=c.taxpayertypecode(+)
And a.gbtradecode=d.gbtradecode(+)
And a.levycode=g.orgdeptcode(+)
And a.taxofficialcode=h.taxofficialcode(+) And a.levycode Like '26103%'
Order By taxregcode)第二个语句:
Select rownum,
taxregcode,
decode(taxpayername,null,'合计',taxpayername) taxpayername,
orgdeptname,
gbtradename,
managetradename,
regtypename,
taxtypename,
decode(shuqi,'至',Null,shuqi) shuqi,
billnumber,
declaredate,
taxfinal
from (Select rownum,
a.taxregcode,
a.taxpayername,
c.orgdeptname,
d.gbtradename,
f.managetradename,
g.regtypename,
e.taxtypename,
to_char(b.taxtermbegin,'yyyy-mm-dd')||'至'||to_char(b.taxtermend,'yyyy-mm-dd') shuqi,
b.billnumber,
to_char(b.declaredate,'yyyy-mm-dd') declaredate,
trim(to_char(sum(b.taxfinal),'999999999990.99')) taxfinal
From tb_reg_taxpayerbasicinfo a,
tb_lvy_levydata b,
tc_pub_taxorgdept c,
tc_pub_gbtrade d,
tc_pub_taxtype e,
tc_pub_managetrade f,
tc_reg_registerationtype g
Where a.taxregcode=b.taxregcode
And b.orgdeptcode=c.orgdeptcode(+)
And a.gbtradecode=d.gbtradecode(+)
And a.managecode=f.managecode(+)
And b.taxtypecode=e.taxtypecode(+)
And a.regtypecode=g.regtypecode(+)
And b.negativeflag='0'
And b.taxfinal>0
And ((b.paymode='02' And b.enterfisctime Is Null)
Or (b.paymode='01' And b.comparetime Is Null ))
And b.paymentterm>=to_date('2008-01-01','YYYY-MM-DD')
And b.paymentterm<to_date('2008-12-31','YYYY-MM-DD')
And b.taxregcode Not Like 'T%'
and b.managedeptcode like '26103%'
Group By Rollup ((a.taxregcode,a.taxpayername,b.billnumber,
d.gbtradename,f.managetradename,g.regtypename,e.taxtypename,c.orgdeptname,
b.taxtermbegin,b.declaredate,b.taxtermend,Rownum))
Order By taxregcode Desc)
第一个语句:
Select Rownum a ,
taxregcode b ,
taxpayername c ,
taxpayertypename d ,
taxpayerid e ,
gbtradename f ,
jurpname g ,
operaddress h ,
opertel i,
taxfinal1 j ,
taxfinal2 k ,
taxtypename2 l,
orgdeptname m,
taxofficialname n
From (Select Rownum,
a.taxregcode,
a.taxpayername,
c.taxpayertypename,
a.taxpayerid,
d.gbtradename,
a.jurpname,
b.operaddress,
b.opertel,
trim(to_char(nvl(tt1.taxfinal1,0),'999999999990.99')) taxfinal1,
trim(to_char(tt2.taxfinal2,'999999999990.99')) taxfinal2,
taxtypename2,
g.orgdeptname,
h.taxofficialname
From tb_reg_taxpayerbasicinfo a,
tb_reg_taxpayerextinfo b,
tc_reg_taxpayertype c,
tc_pub_gbtrade d,
tc_pub_taxorgdept g,
tc_pub_taxofficialspec h,
(Select t.taxregcode taxregcode1,e.taxtypename taxtypename1,sum(t.taxfinal) taxfinal1
From tb_lvy_levydata t,tc_pub_taxtype e
Where t.negativeflag='0'
And t.taxfinal>0
And ((t.paymode='02' And t.enterfisctime Is Null)
Or (t.paymode='01' And t.comparetime Is Null ))
And t.taxregcode Not Like 'T%'
And to_char(t.paymentterm,'yyyy')<'2008'
And t.taxtypecode=e.taxtypecode(+)
Group By taxregcode,e.taxtypename) tt1,
(Select t.taxregcode taxregcode2,e.taxtypename taxtypename2,t.taxtypecode taxtypecode,sum(t.taxfinal) taxfinal2
From tb_lvy_levydata t,tc_pub_taxtype e
Where t.negativeflag='0'
And t.taxfinal>0
And ((t.paymode='02' And t.enterfisctime Is Null)
Or (t.paymode='01' And t.comparetime Is Null ))
And t.taxregcode Not Like 'T%'
And to_char(t.paymentterm,'yyyymm')<='2008'||'01'
And t.taxtypecode=e.taxtypecode(+)
Group By taxregcode,e.taxtypename,t.taxtypecode) tt2
Where a.taxregcode=tt2.taxregcode2
And tt1.taxregcode1(+)=tt2.taxregcode2
And tt1.taxtypename1(+)=tt2.taxtypename2
And a.taxregcode=b.taxregcode
And b.taxregcode=tt2.taxregcode2
And a.taxpayertypecode=c.taxpayertypecode(+)
And a.gbtradecode=d.gbtradecode(+)
And a.levycode=g.orgdeptcode(+)
And a.taxofficialcode=h.taxofficialcode(+) And a.levycode Like '26103%'
Order By taxregcode)第二个语句:
Select rownum,
taxregcode,
decode(taxpayername,null,'合计',taxpayername) taxpayername,
orgdeptname,
gbtradename,
managetradename,
regtypename,
taxtypename,
decode(shuqi,'至',Null,shuqi) shuqi,
billnumber,
declaredate,
taxfinal
from (Select rownum,
a.taxregcode,
a.taxpayername,
c.orgdeptname,
d.gbtradename,
f.managetradename,
g.regtypename,
e.taxtypename,
to_char(b.taxtermbegin,'yyyy-mm-dd')||'至'||to_char(b.taxtermend,'yyyy-mm-dd') shuqi,
b.billnumber,
to_char(b.declaredate,'yyyy-mm-dd') declaredate,
trim(to_char(sum(b.taxfinal),'999999999990.99')) taxfinal
From tb_reg_taxpayerbasicinfo a,
tb_lvy_levydata b,
tc_pub_taxorgdept c,
tc_pub_gbtrade d,
tc_pub_taxtype e,
tc_pub_managetrade f,
tc_reg_registerationtype g
Where a.taxregcode=b.taxregcode
And b.orgdeptcode=c.orgdeptcode(+)
And a.gbtradecode=d.gbtradecode(+)
And a.managecode=f.managecode(+)
And b.taxtypecode=e.taxtypecode(+)
And a.regtypecode=g.regtypecode(+)
And b.negativeflag='0'
And b.taxfinal>0
And ((b.paymode='02' And b.enterfisctime Is Null)
Or (b.paymode='01' And b.comparetime Is Null ))
And b.paymentterm>=to_date('2008-01-01','YYYY-MM-DD')
And b.paymentterm<to_date('2008-12-31','YYYY-MM-DD')
And b.taxregcode Not Like 'T%'
and b.managedeptcode like '26103%'
Group By Rollup ((a.taxregcode,a.taxpayername,b.billnumber,
d.gbtradename,f.managetradename,g.regtypename,e.taxtypename,c.orgdeptname,
b.taxtermbegin,b.declaredate,b.taxtermend,Rownum))
Order By taxregcode Desc)
解决方案 »
- 有关plsql的grant语句。如下:在sqlplus中
- 紧急求助:rollback怎么没用?
- 一个汇总的问题,高手麻烦帮看看
- 求救!!请各位帮忙!谢谢!急用阿!
- 怎样在sql*plus里写SQL对数据库做备份和恢复? 我要实现的是用程序代码控制oracle的备份和恢复请指点一二??
- 使用PL/SQL Developer 创建作业(Create job)的问题
- 请问下面2个查询语句有什么区别,为什么获取到的记录集不一样?
- 求sql语句
- 请教各位大虾:ORACLE中删除表引起的碎片问题?
- 在线等大侠救助,业务逻辑比较复
- 关于linux下用OTL库C++开发ORECLE数据库程序的问题,急!
- unix下抽数导入另外一台unix机器的oracle的定时触发脚本如何实现,请教大家!
/* Formatted on 2008/12/09 18:12 (Formatter Plus v4.8.8) */
SELECT ROWNUM a, taxregcode b, taxpayername c, taxpayertypename d,
taxpayerid e, gbtradename f, jurpname g, operaddress h, opertel i,
taxfinal1 j, taxfinal2 k, taxtypename2 l, orgdeptname m,
taxofficialname n
FROM (SELECT ROWNUM, a.taxregcode, a.taxpayername, c.taxpayertypename,
a.taxpayerid, d.gbtradename, a.jurpname, b.operaddress,
b.opertel,
TRIM (TO_CHAR (NVL (tt1.taxfinal1, 0), '999999999990.99')
) taxfinal1,
TRIM (TO_CHAR (tt2.taxfinal2, '999999999990.99')) taxfinal2,
taxtypename2, g.orgdeptname, h.taxofficialname
FROM tb_reg_taxpayerbasicinfo a,
tb_reg_taxpayerextinfo b,
tc_reg_taxpayertype c,
tc_pub_gbtrade d,
tc_pub_taxorgdept g,
tc_pub_taxofficialspec h,
(SELECT t.taxregcode taxregcode1,
e.taxtypename taxtypename1,
SUM (t.taxfinal) taxfinal1
FROM tb_lvy_levydata t, tc_pub_taxtype e
WHERE t.negativeflag = '0'
AND t.taxfinal > 0
AND ( (t.paymode = '02' AND t.enterfisctime IS NULL
)
OR (t.paymode = '01' AND t.comparetime IS NULL)
)
AND t.taxregcode NOT LIKE 'T%'
AND TO_CHAR (t.paymentterm, 'yyyy') < '2008'
AND t.taxtypecode = e.taxtypecode(+)
GROUP BY taxregcode, e.taxtypename) tt1,
(SELECT t.taxregcode taxregcode2,
e.taxtypename taxtypename2,
t.taxtypecode taxtypecode,
SUM (t.taxfinal) taxfinal2
FROM tb_lvy_levydata t, tc_pub_taxtype e
WHERE t.negativeflag = '0'
AND t.taxfinal > 0
AND ( (t.paymode = '02' AND t.enterfisctime IS NULL
)
OR (t.paymode = '01' AND t.comparetime IS NULL)
)
AND t.taxregcode NOT LIKE 'T%'
AND TO_CHAR (t.paymentterm, 'yyyymm') <= '2008' || '01'
AND t.taxtypecode = e.taxtypecode(+)
GROUP BY taxregcode, e.taxtypename, t.taxtypecode) tt2
WHERE a.taxregcode = tt2.taxregcode2
AND tt1.taxregcode1(+) = tt2.taxregcode2
AND tt1.taxtypename1(+) = tt2.taxtypename2
AND a.taxregcode = b.taxregcode
AND b.taxregcode = tt2.taxregcode2
AND a.taxpayertypecode = c.taxpayertypecode(+)
AND a.gbtradecode = d.gbtradecode(+)
AND a.levycode = g.orgdeptcode(+)
AND a.taxofficialcode = h.taxofficialcode(+)
AND a.levycode LIKE '26103%'
ORDER BY taxregcode)/* Formatted on 2008/12/09 18:13 (Formatter Plus v4.8.8) */
SELECT ROWNUM, taxregcode,
DECODE (taxpayername, NULL, '合计', taxpayername) taxpayername,
orgdeptname, gbtradename, managetradename, regtypename, taxtypename,
DECODE (shuqi, '至', NULL, shuqi) shuqi, billnumber, declaredate,
taxfinal
FROM (SELECT ROWNUM, a.taxregcode, a.taxpayername, c.orgdeptname,
d.gbtradename, f.managetradename, g.regtypename,
e.taxtypename,
TO_CHAR (b.taxtermbegin, 'yyyy-mm-dd')
|| '至'
|| TO_CHAR (b.taxtermend, 'yyyy-mm-dd') shuqi,
b.billnumber,
TO_CHAR (b.declaredate, 'yyyy-mm-dd') declaredate,
TRIM (TO_CHAR (SUM (b.taxfinal), '999999999990.99'))
taxfinal
FROM tb_reg_taxpayerbasicinfo a,
tb_lvy_levydata b,
tc_pub_taxorgdept c,
tc_pub_gbtrade d,
tc_pub_taxtype e,
tc_pub_managetrade f,
tc_reg_registerationtype g
WHERE a.taxregcode = b.taxregcode
AND b.orgdeptcode = c.orgdeptcode(+)
AND a.gbtradecode = d.gbtradecode(+)
AND a.managecode = f.managecode(+)
AND b.taxtypecode = e.taxtypecode(+)
AND a.regtypecode = g.regtypecode(+)
AND b.negativeflag = '0'
AND b.taxfinal > 0
AND ( (b.paymode = '02' AND b.enterfisctime IS NULL)
OR (b.paymode = '01' AND b.comparetime IS NULL)
)
AND b.paymentterm >= TO_DATE ('2008-01-01', 'YYYY-MM-DD')
AND b.paymentterm < TO_DATE ('2008-12-31', 'YYYY-MM-DD')
AND b.taxregcode NOT LIKE 'T%'
AND b.managedeptcode LIKE '26103%'
GROUP BY ROLLUP ((a.taxregcode,
a.taxpayername,
b.billnumber,
d.gbtradename,
f.managetradename,
g.regtypename,
e.taxtypename,
c.orgdeptname,
b.taxtermbegin,
b.declaredate,
b.taxtermend,
ROWNUM
))
ORDER BY taxregcode DESC)