请教个问题:我现在有一张任务表task,里面有一个type属性,若是计划的任务,该值为p,若是执行的任务,该值为a,还有一个表示几个小时的属性“hours”,还有一个emp属性,表示当前记录是属于哪个员工的,我能不能用一条SQL,按人员emp,分别统计出来计划的总时间和执行的总时间?
表结构如下:
task:
id emp type hours
1 cn001 a 4
2 cn001 p 4
3 cn001 a 4
4 cn002 a 4
5 cn002 p 4
6 cn002 p 4我想得到的结果:
emp hours(a) hours(p)
cn001 8 4
cn002 4 8
我现在的SQL是这样的:select sum(hours),emp from task group by emp,type但是出来的结果不是我想要的结果,要怎样改呀?
表结构如下:
task:
id emp type hours
1 cn001 a 4
2 cn001 p 4
3 cn001 a 4
4 cn002 a 4
5 cn002 p 4
6 cn002 p 4我想得到的结果:
emp hours(a) hours(p)
cn001 8 4
cn002 4 8
我现在的SQL是这样的:select sum(hours),emp from task group by emp,type但是出来的结果不是我想要的结果,要怎样改呀?
sum(case type when 'p' then hours else 0 end) hours(p)
from task
group by emp
decode(type,'p',sum(hours)) as hours(p)
from task
group by emp,type
from task group by emp order by emp;
select emp,
sum(case type when 'a' then hours else 0 end ) hours(a) ,
sum(case type when '6' then hours else 0 end ) hours(b)
from task
group by emp
sum(decode(type,'a',hours,0)) as hours(a),
sum(decode(type,'b',hours,0)) as hours(b)
from task
group by emp
SQL> select * from t; ID EMP TYPE HOURS
--------------------------------------- -------------------- ---- ---------------------------------------
1 cn001 a 4
2 cn001 p 4
3 cn001 a 4
4 cn002 a 4
5 cn002 p 4
6 cn002 p 46 rows selectedSQL>
SQL> select emp,sum(decode(type,'a',hours,0)) as "hours(a)",sum(decode(type,'p',hours,0)) as "hours(p)"
2 from t
3 group by emp;EMP hours(a) hours(p)
-------------------- ---------- ----------
cn001 8 4
cn002 4 8SQL
sum(decode(type,'p',hours,0)) as hours-p
from task
group by emp
2 sum(decode(type,'p',hours,0)) as "hours(p)"
3 from t1
4 group by emp;EMP hours(a) hours(p)
---------- ---------- ----------
333 34 0
222 67 2
111 22 44SQL> select * from t1;EMP HOURS TY
---------- ---------- --
111 22 a
222 2 p
333 34 a
111 44 p
222 45 a
222 22 a已选择6行。SQL>
create table tb ( id int ,emp varchar(10),type varchar(2), hours int)insert into tb select 1 , 'cn001' , 'a' , 4
insert into tb select 2 , 'cn001' , 'p' , 4
insert into tb select 3 , 'cn001' , 'a' , 4
insert into tb select 4 , 'cn002' , 'a' , 4
insert into tb select 5 , 'cn002' , 'p' , 4
insert into tb select 6 , 'cn002' , 'p' , 4
select sum(case when type = 'a' then hours else 0 end),sum(case when type = 'p' then hours else 0 end),emp from tb group by emp drop table tb