你的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 客户端库时引发 BadImageFormatException。如果在安装 32 位 Oracle 客户端组件的情况下以 64 位模式运
- 征集一个查询语句
- 如何一次导出所有表的索引
- 求一条 sql 语句
- oracle将一个结果集作为模糊查询条件改怎么写
- 通过触发器处理当前标记项CURRENT_FLAG的赋值问题,请各位帮忙,急!
- 查询嵌套表问题,如何展开嵌套表内容?
- Oracle连接问题
- 请教一个 linux 上安装oracle11g 提示无法使用命令 /usr/bin/xdpyinfo 问题谢谢
- 如何加快查询速度?急!
- oracle9i中如何实现表中数据的行列转换,用一条sql语句实现。
有些有条件的统计用decode,可以化简许多