你的sql效率太差,没这种写法。下面是一个例子,你看懂了问题就解决了。SQL> create table test
2 (company_id varchar2(30),
3 pay_month varchar2(6),
4 amount float);
Table created.
SQL> insert into test
2 values ('a', '200401', 1000);
1 row created.
SQL> insert into test
2 values ('a', '200402', 800);
1 row created.
SQL> insert into test
2 values ('a', '200403', 1200);
1 row created.SQL> select company_id, sum (amount) ttl_amount
2 , sum (decode (substr (pay_month, 5, 2), '01', amount, 0)) jan
3 , sum (decode (substr (pay_month, 5, 2), '02', amount, 0)) feb
4 , sum (decode (substr (pay_month, 5, 2), '03', amount, 0)) mar
5 from test
6 where pay_month between '200401' and '200412'
7 group by company_id;COMPANY_ID TTL_AMOUNT JAN FEB MAR
------------------------------ ---------- ---------- ---------- ----------
a 3000 1000 800 1200
2 (company_id varchar2(30),
3 pay_month varchar2(6),
4 amount float);
Table created.
SQL> insert into test
2 values ('a', '200401', 1000);
1 row created.
SQL> insert into test
2 values ('a', '200402', 800);
1 row created.
SQL> insert into test
2 values ('a', '200403', 1200);
1 row created.SQL> select company_id, sum (amount) ttl_amount
2 , sum (decode (substr (pay_month, 5, 2), '01', amount, 0)) jan
3 , sum (decode (substr (pay_month, 5, 2), '02', amount, 0)) feb
4 , sum (decode (substr (pay_month, 5, 2), '03', amount, 0)) mar
5 from test
6 where pay_month between '200401' and '200412'
7 group by company_id;COMPANY_ID TTL_AMOUNT JAN FEB MAR
------------------------------ ---------- ---------- ---------- ----------
a 3000 1000 800 1200
解决方案 »
- 数据备份遇到的菜鸟问题
- oracle双表合并查询
- Oracle数据库往表中插入新字段遇到的问题,详情看问题补充
- 不显示删除回复显示所有回复显示星级回复显示得分回复 十分紧急的问题,紧急求助。oracle9i的left join有状况[
- ORA-00600: internal error code, arguments: [KGHALP1], [0x0], [], [], [], [], [], []
- 怎么把这五条SQL语句合并成一条SQL语句?
- 我修改了计算机名称后,oracle8.1.6数据库登录不进去了
- 着急,一个导出的文件有5G,可是数据文件最大不能超过4G,如何导入??(解决问题追加50分)
- 如何不通过用户名和密码用SQLPLUS登录数据库?
- ora-10632 invalid rowid
- 如何加快查询速度?急!
- oracle9i中如何实现表中数据的行列转换,用一条sql语句实现。
有些有条件的统计用decode,可以化简许多