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

;