17,086
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE T138
(
ID VARCHAR2(20),
YearMonth INTEGER,
ShouRu NUMBER(10),
BuMen VARCHAR2(20)
);
INSERT INTO T138 VALUES('00011', 200601, 100, '001');
INSERT INTO T138 VALUES('00011', 200602, 200, '001');
INSERT INTO T138 VALUES('00011', 200603, 300, '001');
INSERT INTO T138 VALUES('00011', 200701, 1000, '001');
INSERT INTO T138 VALUES('00011', 200702, 2000, '001');
INSERT INTO T138 VALUES('00011', 200703, 3000, '001');
INSERT INTO T138 VALUES('00011', 200801, 10000, '001');
INSERT INTO T138 VALUES('00011', 200802, 20000, '001');
INSERT INTO T138 VALUES('00011', 200803, 30000, '001');
INSERT INTO T138 VALUES('00012', 200601, 1, '001');
INSERT INTO T138 VALUES('00012', 200602, 2, '001');
INSERT INTO T138 VALUES('00012', 200603, 3, '001');
INSERT INTO T138 VALUES('00012', 200701, 10, '001');
INSERT INTO T138 VALUES('00012', 200702, 20, '001');
INSERT INTO T138 VALUES('00012', 200703, 30, '001');
INSERT INTO T138 VALUES('00012', 200801, 100, '001');
INSERT INTO T138 VALUES('00012', 200802, 200, '001');
INSERT INTO T138 VALUES('00012', 200803, 300, '001');
INSERT INTO T138 VALUES('00013', 200601, 1000, '001');
INSERT INTO T138 VALUES('00013', 200602, 2000, '001');
INSERT INTO T138 VALUES('00013', 200603, 3000, '001');
INSERT INTO T138 VALUES('00013', 200701, 10000, '001');
INSERT INTO T138 VALUES('00013', 200702, 20000, '001');
INSERT INTO T138 VALUES('00013', 200703, 30000, '001');
INSERT INTO T138 VALUES('00013', 200801, 100000, '001');
INSERT INTO T138 VALUES('00013', 200802, 200000, '001');
INSERT INTO T138 VALUES('00013', 200803, 300000, '001');
select ID,
year2 ,
decode(year2,year1, quannianshouru ,0),
decode(year2,year1, bumen,'')
from(
select ID, t2.year year2 ,quannianshouru ,bumen ,t1.year year1 from
(select id, substr(year_month ,1,4) year, sum(shouru) quannianshouru , bumen
from tb
group by id,substr(year_month ,1,4),bumen)t1 ,
(select 20||lpad(rownum,2,'0') year from dual connect by rownum<=11)t2
)
select id, substr(year_month ,0,4) year, sum(shouru) shouru, bumen
from sa
group by id,substr(year_month ,0,4),bumen
select id, to_char(year_month ,'YYYY') year, sum(shouru), bumen
from sa
group by id,to_char(year_month ,'YYYY'),bumen
select id, substr(year_month ,0,4) year, sum(shouru), bumen
from sa
group by id,substr(year_month ,0,4),bumen