业务需求:按机构亮灯数排序,先按红灯数排序,再按黄灯数排序;如果没有这个机构,默认红黄灯都是0;总机构,东南区,西北区按顺序默认排在最前面
一,sql解决
select
deptCode,
deptName,
case when deptCode ='2' then 1 when deptCode ='01' then '2' when deptCode ='02' then '3' end as flag,--这三个机构按顺序排 --在最前面
--下面行转列
sum(case when warnLightType ='0' then lightNum else 0 end) redLight ,--红灯
sum(case when warnLightType ='1' then lightNum else 0 end) yellowLight --黄灯
from (
select
xx.dept_code deptCode,
xx.dept_name deptName,
yy.warnLightType warnLightType,
count(yy.warnLightType) lightNum
from
(
select
deptName,
deptCode,
warnLightType
from
......省略
) yy right join