group by 返回null( 不存在) 如何使用case when
1. 数据如下:
2. 有数据的情况:
select DEPT, group_concat(distinct level) as level from content where NAME = '南京' group by dept
select DEPT, case when level like '%2%' then 0 when level like '%3%' then 1 when level like '%4%' then 2 when level like '%0%' then 3 end as newlevel, case when count(level) =0 then '灰' when level like '%2%' then '红' when level like '%3%' then '黄' when level like '%4%' then '绿' when level like '%0%' then '灰' end as colour from ( select DEPT, group_concat(distinct level) as level from content where NAME = '南京' group by dept ) t ;
3. 无数据的情况:
select DEPT, group_concat(distinct level) as level from content where NAME = '南1京' group by dept
select DEPT, case when count(level) =0 then 3 when level like '%2%' then 0 when level like '%3%' then 1 when level like '%4%' then 2 when level like '%0%' then 3 end as newlevel, case when count(level) =0 then '灰' when level like '%2%' then '红' when level like '%3%' then '黄' when level like '%4%' then '绿' when level like '%0%' then '灰' end as colour from ( select DEPT, group_concat(distinct level) as level from content where NAME = '南123京' group by dept ) t ;
select
DEPT,
case
when count(level) =0 then 3
when level like '%2%' then 0
when level like '%3%' then 1
when level like '%4%' then 2
when level like '%0%' then 3
end as newlevel,
case
when count(level) =0 then '灰'
when level like '%2%' then '红'
when level like '%3%' then '黄'
when level like '%4%' then '绿'
when level like '%0%' then '灰'
end as colour
from
(
select
DEPT,
group_concat(level) as level
from
content
where
NAME = '南京'
group by
dept
) t
;
钟声敲响了日落,柏油路跃过山坡,一直通向北方的是我们想象,长大后也未曾经过~