这个是我写的sql语句
select count(考勤天数),员工ID,月份 from 考勤表 group by 员工ID,月份;
下边这个是查询出来的结果:
考勤天数 员工ID 月份
28 A 4
26 A 3
25 A 2
28 B 3
26 B 2
27 C 4
30 C 3
27 C 2
我想让它查询出这样的显示结果怎么做呢?
员工ID 4月 3月 2月
A 28 26 25
B 0 28 26
C 27 30 27
要用oracle中的sql实现,亲们,没人帮就回不去了。
...全文
7545打赏收藏
大过年的,求助oracle问题
这个是我写的sql语句 select count(考勤天数),员工ID,月份 from 考勤表 group by 员工ID,月份; 下边这个是查询出来的结果: 考勤天数 员工ID 月份 28 A 4 26 A 3 25 A 2 28 B 3 26 B 2 27 C 4 30 C 3 27 C 2 我想让它查询出这样的显示结果怎么做呢? 员工ID 4月 3月 2月 A 28 26 25 B 0 28 26 C 27 30 27 要用oracle中的sql实现,亲们,没人帮就回不去了。
SAMPLE:
create table test (times number, months number, emp varchar2(20));
--drop table test;
insert into test values(28,2,'A');
insert into test values(29,3,'A');
insert into test values(30,4,'A');
insert into test values(31,3,'B');
insert into test values(29,4,'B');
insert into test values(26,2,'C');
insert into test values(30,3,'C');
insert into test values(27,4,'C');
SELECT * FROM TEST;
TIMES MONTHS EMP
1 28 2 A
2 29 3 A
3 30 4 A
4 31 3 B
5 29 4 B
6 26 2 C
7 30 3 C
8 27 4 C
select EMP, SUM(DECODE(MONTHS, 2, TIMES, 0)) AS FEB
,SUM(DECODE(MONTHS, 3, TIMES, 0)) AS MARCH
,SUM(DECODE(MONTHS, 4, TIMES, 0)) AS APRIL from test GROUP BY EMP;
EMP FEB MARCH APRIL
1 A 28 29 30
2 B 0 31 29
3 C 26 30 27
select b,max(decode(c,4,a,0)) as April,max(decode(c,3,a,0)) as March,max(decode(c,2,a,0)) as Feb
from t group by b
--11g
SELECT * FROM t PIVOT(sum(a) FOR c IN (4 as April, 3 as March, 2 as Feb))
--sql
with T
as
(select 28 a , 'A' b , 4 c from dual
union all
select 26 , 'A' , 3 from dual
union all
select 25 , 'A' , 2 from dual
union all
select 28 , 'B' , 3 from dual
union all
select 26 , 'B' , 2 from dual
union all
select 27 , 'C' , 4 from dual
union all
select 30 , 'C' , 3 from dual
union all
select 27 , 'C' , 2 from dual
)
select b,sum(case when c=4 then a else 0 end) as April,
sum(case when c=3 then a else 0 end) as March,
sum(case when c=2 then a else 0 end) as Feb
from t group by b
--result:
A 28 26 25
B 0 28 26
C 27 30 27