year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
===================
已有如下解法:
--1M:
SELECT YEAR,
SUM(CASE WHEN MONTH='1' THEN AMOUNT END) M1,
SUM(CASE WHEN MONTH='2' THEN AMOUNT END) M2,
SUM(CASE WHEN MONTH='3' THEN AMOUNT END) M3,
SUM(CASE WHEN MONTH='4' THEN AMOUNT END) M4
FROM AAA
GROUP BY YEAR --2M:
select year,
(select amount from aaa m where month=1 and m.year=b.year) as m1,
(select amount from aaa m where month=2 and m.year=b.year) as m2,
(select amount from aaa m where month=3 and m.year=b.year) as m3,
(select amount from aaa m where month=4 and m.year=b.year) as m4
from aaa b group by year --3M: maybe only used on oracle
SELECT YEAR,
MAX(DECODE(MONTH,1,AMOUNT,0)) M1,
MAX(DECODE(MONTH,2,AMOUNT,0)) M2,
MAX(DECODE(MONTH,3,AMOUNT,0)) M3,
MAX(DECODE(MONTH,4,AMOUNT,0)) M4
FROM AAA
GROUP BY YEAR
但是感觉这些写法有点局限,都把月给写死了,如果月份是变化的,好像就有问题,请高手们看看,谢谢!
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
===================
已有如下解法:
--1M:
SELECT YEAR,
SUM(CASE WHEN MONTH='1' THEN AMOUNT END) M1,
SUM(CASE WHEN MONTH='2' THEN AMOUNT END) M2,
SUM(CASE WHEN MONTH='3' THEN AMOUNT END) M3,
SUM(CASE WHEN MONTH='4' THEN AMOUNT END) M4
FROM AAA
GROUP BY YEAR --2M:
select year,
(select amount from aaa m where month=1 and m.year=b.year) as m1,
(select amount from aaa m where month=2 and m.year=b.year) as m2,
(select amount from aaa m where month=3 and m.year=b.year) as m3,
(select amount from aaa m where month=4 and m.year=b.year) as m4
from aaa b group by year --3M: maybe only used on oracle
SELECT YEAR,
MAX(DECODE(MONTH,1,AMOUNT,0)) M1,
MAX(DECODE(MONTH,2,AMOUNT,0)) M2,
MAX(DECODE(MONTH,3,AMOUNT,0)) M3,
MAX(DECODE(MONTH,4,AMOUNT,0)) M4
FROM AAA
GROUP BY YEAR
但是感觉这些写法有点局限,都把月给写死了,如果月份是变化的,好像就有问题,请高手们看看,谢谢!
==================
确实是老问题,以前的一个朋友的帖子,我重提的,感觉答案不是十分满意,呵呵.现在可以这样写:
SELECT YEAR,
MAX(DECODE(MONTH,1,AMOUNT,0)) M1,...,MAX(DECODE(MONTH,1,AMOUNT,0)) M12 FROM AAA
GROUP BY YEAR
把所有的月份都列出来就ok了,不过期待高手给出更好的方法,谢谢啊!
大侠能不能出个不用动态sql的例子啊,最好能一句话搞定的,呵呵
一搜就搜到了~~虽然简单,但是要写很多~~~~
LZ应该能自己解决~
把头像换回来了啊,呵呵,
那个看起来像小丫头用的找到了mantisXF的一个动态的,用的过程:
http://topic.csdn.net/u/20081111/15/c9ea0d19-af1c-41ed-befe-d56258ec12e3.html
你回的我记得存了网摘的,太多了没找到,呵呵
---像下面这样解决有点别扭,只能算一种解决方式而已,期待大家更好的办法。(我这边是表关联)SQL> select * from a;SJ ZTBM LSH
----------- ---------- ----------
2008-10-1 1001 20080001
2008-10-1 1001 20080002
2008-10-1 1002 20080003
2008-10-1 1003 20080004
2008-10-1 1003 20080005
2008-10-2 1002 20080006
2008-10-2 1002 20080007
2008-10-2 1001 200800088 rows selectedSQL> select * from b;ZTBM ZTM
---------- --------------------
1001 状态名1
1002 状态名2
1003 状态名3
1004 状态名4SQL>
SQL> create or replace procedure proc_test as
2 cursor cur_b is
3 select * from b;
4 v_sql varchar2(2000);
5 i number;
6 begin
7 v_sql := 'create or replace view view_sleepzzzzz as select to_char(sj,''yyyy-mm-dd'') sj,';
8 for c_temp in cur_b loop
9 v_sql := v_sql || 'sum(decode(ztbm,''' || c_temp.ztbm || ''',1,0)) ' ||
10 c_temp.ztm || ',';
11 end loop;
12 v_sql := substr(v_sql, 1, length(v_sql) - 1) || ' from a group by to_char(sj,''yyyy-mm-dd'')';
13 execute immediate v_sql;
14 end proc_test;
15 /Procedure createdSQL> exec proc_test;PL/SQL procedure successfully completedSQL> select * from view_sleepzzzzz;SJ 状态名1 状态名2 状态名3 状态名4
----------- ---------- ---------- ---------- ----------
2008-10-1 2 1 2 0
2008-10-2 1 2 0 0
/* -- 以下是动态增加一个状态测试
SQL> select * from b;ZTBM ZTM
---------- --------------------
1001 状态名1
1002 状态名2
1003 状态名3
1004 状态名4
1005 状态名5SQL> exec proc_test;PL/SQL procedure successfully completedSQL> select * from view_sleepzzzzz;SJ 状态名1 状态名2 状态名3 状态名4 状态名5
----------- ---------- ---------- ---------- ---------- ----------
2008-10-1 2 1 2 0 0
2008-10-2 1 2 0 0 0SQL>
*/
http://topic.csdn.net/u/20081104/18/d5cb35e2-d478-4d0b-90be-7443795ba61c.html