sql-server-2008 - 按问题分组

Question

select 
        case status
            when 'AR' then 1
            when 'AV' then 2
            when 'AN' then 3
            when 'AD' then 4
            when 'AC' then 5
            when 'AY' then 6
            when 'AH' then 7
            else 8
        end 'status_order', COUNT(*)
from dn_appeal_request
group by 
        case status
            when 'AR' then 1
            when 'AV' then 2
            when 'AN' then 3
            when 'AD' then 4
            when 'AC' then 5
            when 'AY' then 6
            when 'AH' then 7
            else 8
        end 

有没有办法在不使用select中使用相同case语句的情况下进行分组？

Answer 1

您可以通过使用子查询来避免重复执行case语句。您确实需要对值进行分组。

select D.status_order, COUNT(*)
from (
        select case status
                 when 'AR' then 1
                 when 'AV' then 2
                 when 'AN' then 3
                 when 'AD' then 4
                 when 'AC' then 5
                 when 'AY' then 6
                 when 'AH' then 7
                 else 8
               end 'status_order'
        from dn_appeal_request
     ) as D
group by D.status_order