现在小弟有一张表,如下:
ID year_month shouru bumen
00011 200601 3400.5 001
00011 200602 3412.3 001
00012 200703 2300 002ID是员工的号码,shouru是该员工月份的收入,bumen是员工的部门代号
现在我要根据这张表重建一张新表,表如下:
ID year quannianshouru bumen
00011 2006 34000.43 001
00011 2007 35002.5 001
00012 2008 23680.7 002新表只统计某一员工某年的全部收入(表中一共有2000年到2011年的数据),所以每个员工对应了11条数据。谢谢各位高手帮忙,多谢
ID year_month shouru bumen
00011 200601 3400.5 001
00011 200602 3412.3 001
00012 200703 2300 002ID是员工的号码,shouru是该员工月份的收入,bumen是员工的部门代号
现在我要根据这张表重建一张新表,表如下:
ID year quannianshouru bumen
00011 2006 34000.43 001
00011 2007 35002.5 001
00012 2008 23680.7 002新表只统计某一员工某年的全部收入(表中一共有2000年到2011年的数据),所以每个员工对应了11条数据。谢谢各位高手帮忙,多谢
select id, substr(year_month ,0,4) year, sum(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
create table new_shouru (id,year,quannianshouru,bumen) as
select a.id,to_char(a.year_month,'YYYY'),sum(a.shouru),a.bumen
from shouru a group by a.id,to_char(a.year_month,'YYYY'),a.bumen;
select id, substr(year_month ,0,4) year, sum(shouru) shouru, bumen
from sa
group by id,substr(year_month ,0,4),bumen
shouru是员工的月收入,number类型
year_month 是用来记录日期的,但是是interger类型,如2006年一月份只需输200601,
bumen 是员工所属的部门代号,是varchar类型的
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
)
from sa
group by id,substr(year_month ,0,4),bumen--分组--这个有什么需要解释的??
楼主还是要多看看语法。
(
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');实测结果:
from sa
group by id,year_month/100,bumen其实上你的表sa 中 bumen冗余字段。
因为员工信息表中才应存在bumen