项目场景:
mysql学习
问题描述:
表中数据如下:
dept_id | status | ratio |
A | 0 | 0.1 |
B | 1 | 0.2 |
C | 0 | 0.3 |
A | 3 | 0.4 |
B | 2 | 0.5 |
输出数据如下:
dept_id | status_0 | status_1 | status_2 | status_3 |
A | sum(ratio) | sum(ratio) | sum(ratio) | sum(ratio) |
B | sum(ratio) | sum(ratio) | sum(ratio) | sum(ratio) |
C | sum(ratio) | sum(ratio) | sum(ratio) | sum(ratio) |
解决方案:
#方案一:
select
dept_id as dept_id,
count(*) as num,
count(*) as jiaGong,
0 as guZhang,
0 as kongXian,
0 as guanJi,
sum(utilize_ratio) as utilize_ratio,
sum(run_time) as run_time,
sum(fault_time) as fault_time,
sum(free_time) as free_time,
sum(close_time) as close_time
from
adm_gallop_machine_worktime_today
where
machine_location <> '暂不考核'
and command_name='加工'
group by dept_id
union
select
dept_id,
count(*),
0,
count(*),
0,
0,
sum(utilize_ratio),
sum(run_time),
sum(fault_time),
sum(free_time),
sum(close_time)
from
adm_gallop_machine_worktime_today
where
machine_location <> '暂不考核'
and command_name='网络故障'
group by dept_id
union
select
dept_id,
count(*),
0,
0,
count(*),
0,
sum(utilize_ratio),
sum(run_time),
sum(fault_time),
sum(free_time),
sum(close_time)
from
adm_gallop_machine_worktime_today
where
machine_location <> '暂不考核'
and command_name='空闲'
group by dept_id
union
select
dept_id,
count(*),
0,
0,
0,
count(*),
sum(utilize_ratio),
sum(run_time),
sum(fault_time),
sum(free_time),
sum(close_time)
from
adm_gallop_machine_worktime_today
where
machine_location <> '暂不考核'
and command_name='关机'
group by dept_id;
#方案二:
select
dept_id as fir,
count(*),
sum( case when command_name = '加工' then 1 else 0 end ),
sum( case when command_name = '网络故障' then 1 else 0 end ),
sum( case when command_name = '空闲' then 1 else 0 end ),
sum( case when command_name = '关机' then 1 else 0 end ),
sum(utilize_ratio)/count(*)*100,
sum( run_time ) as run_time,
sum( fault_time ) as fault_time,
sum( free_time ) as free_time,
sum( close_time ) as close_time
from
adm_gallop_machine_worktime_today
where
machine_location <> '暂不考核'
group by
dept_id;