mysql代码

项目场景：

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;