表:employees
JOB_ID SALARY DEPARTMENT_ID
---------- ---------- -------------
ad_pres 24000.00 90
ad_vp 17000.00 90
ad_vp 17000.00 90
it_prog 9000.00 60
it_prog 6000.00 60
it_prog 4200.00 60
st_man 5800.00 50
st_clerk 3500.00 50
st_clerk 3100.00 50
st_clerk 2600.00 50
st_clerk 2500.00 50
sa_man 10500.00 80
sa_rep 11000.00 80
sa_rep 8600.00 80
sa_rep 7000.00
ad_asst 4400.00 10
mk_man 13000.00 20
mk_rep 6000.00 20
ac_mcr 12000.00 110
ac_account 8300.00 110所要的结果:
ac_account 8300
ac_mcr 12000
ad_asst 4400
ad_pres 24000 24000
ad_vp 34000 34000
it_prog 19200
mk_man 13000 13000
mk_rep 6000 6000
sa_man 10500 10500
sa_rep 19600 26600
st_clerk 11700 11700
st_man 5800 5800我写的sql语句:
select job_id,
decode(department_id,'20',sum(salary)) "dep 20",
decode(department_id,'50',sum(salary)) "dep 50",
decode(department_id,'80',sum(salary) )"dep 80",
decode(department_id,'90',sum(salary)) "dep 90",
sum(salary) total
from employees
group by job_id,department_id
order by job_id结果为:
JOB_ID dep 20 dep 50 dep 80 dep 90 TOTAL
---------- ---------- ---------- ---------- ---------- ----------
ac_account 8300
ac_mcr 12000
ad_asst 4400
ad_pres 24000 24000
ad_vp 34000 34000
it_prog 19200
mk_man 13000 13000
mk_rep 6000 6000
sa_man 10500 10500
sa_rep 19600 19600
sa_rep 7000
st_clerk 11700 11700
st_man 5800 5800请问大家:如何把sa_rep 合并,salary=7000那个值,没有department_id?
JOB_ID SALARY DEPARTMENT_ID
---------- ---------- -------------
ad_pres 24000.00 90
ad_vp 17000.00 90
ad_vp 17000.00 90
it_prog 9000.00 60
it_prog 6000.00 60
it_prog 4200.00 60
st_man 5800.00 50
st_clerk 3500.00 50
st_clerk 3100.00 50
st_clerk 2600.00 50
st_clerk 2500.00 50
sa_man 10500.00 80
sa_rep 11000.00 80
sa_rep 8600.00 80
sa_rep 7000.00
ad_asst 4400.00 10
mk_man 13000.00 20
mk_rep 6000.00 20
ac_mcr 12000.00 110
ac_account 8300.00 110所要的结果:
ac_account 8300
ac_mcr 12000
ad_asst 4400
ad_pres 24000 24000
ad_vp 34000 34000
it_prog 19200
mk_man 13000 13000
mk_rep 6000 6000
sa_man 10500 10500
sa_rep 19600 26600
st_clerk 11700 11700
st_man 5800 5800我写的sql语句:
select job_id,
decode(department_id,'20',sum(salary)) "dep 20",
decode(department_id,'50',sum(salary)) "dep 50",
decode(department_id,'80',sum(salary) )"dep 80",
decode(department_id,'90',sum(salary)) "dep 90",
sum(salary) total
from employees
group by job_id,department_id
order by job_id结果为:
JOB_ID dep 20 dep 50 dep 80 dep 90 TOTAL
---------- ---------- ---------- ---------- ---------- ----------
ac_account 8300
ac_mcr 12000
ad_asst 4400
ad_pres 24000 24000
ad_vp 34000 34000
it_prog 19200
mk_man 13000 13000
mk_rep 6000 6000
sa_man 10500 10500
sa_rep 19600 19600
sa_rep 7000
st_clerk 11700 11700
st_man 5800 5800请问大家:如何把sa_rep 合并,salary=7000那个值,没有department_id?
解决方案 »
- ORA-01126: 数据库必须已装载到此实例并且不在任何实例中打开
- sql select 效率问题 高手快来看看
- ORACLE-9i怎样实现增量备份
- dbms_scheduler的repeat_interval设置问题
- 有关v$session表中状态的问题?!
- HttpServer服务是做什么用的?
- 批处理按日期备份oracle数据库并只保留一个月的数据
- 高人请指点,这个结果SYS_LOB0000031469C00016$$是什么东西,如何才能释放该空间。急!!!!!
- SPFILEDLLAND.ORA作用是什么oreacle 9i
- 寻求SELECT的写法?
- oracle表空间使用情况为什么不变化啊
- 基于时间的不完全备份 问题。
把这句改成group by job_id
sum(decode(department_id,'20',salary,0)) "dep 20", --如果是20,加进去,否则0
sum(decode(department_id,'50',salary,0)) "dep 50", --同上
sum(decode(department_id,'80',salary,0)) "dep 80", --同上
sum(decode(department_id,'90',salary,0)) "dep 90", --同上
sum(salary) total
from employees
group by job_id
order by job_id
试试看?