SQL Server case when or enum

Question

我有一个类似于的表

stuff          type      price
first_stuff     1       43
second_stuff    2       46
third_stuff     3       24
fourth_stuff    2       12
fifth_stuff     NULL    90

并且为每一个type的东西分配一个不存储在DB 中的描述

1 = Bad
2 = Good
3 = Excellent 
NULL = Not_Assigned

我只想返回一个表，其中count和type分别对应，类似于：

Description      Count
Bad              1
Good             2
Excellent        1
Not_Assigned     1

Answer 1

DECLARE @t TABLE ([type] INT)
INSERT INTO @t ([type])
VALUES (1),(2),(3),(2),(NULL)
SELECT
    [Description] =
        CASE t.[type]
            WHEN 1 THEN 'Bad'
            WHEN 2 THEN 'Good'
            WHEN 3 THEN 'Excellent'
            ELSE 'Not_Assigned'
        END, t.[Count]
FROM (
    SELECT [type], [Count] = COUNT(*)
    FROM @t
    GROUP BY [type]
) t
ORDER BY ISNULL(t.[type], 999)

输出-

Description  Count
------------ -----------
Bad          1
Good         2
Excellent    1
Not_Assigned 1

Answer 2

;WITH CTE_TYPE
     AS (SELECT DESCRIPTION,
                VALUE
         FROM   (VALUES ('BAD',
                1),
                        ('GOOD',
                2),
                        ('EXCELLENT',
                3))V( DESCRIPTION, VALUE )),
     CTE_COUNT
     AS (SELECT C.DESCRIPTION,
                Count(T.TYPE) TYPE_COUNT
         FROM   YOUR_TABLE T
                JOIN CTE_TYPE C
                  ON T.TYPE = C.VALUE
         GROUP  BY TYPE,
                   DESCRIPTION
         UNION ALL
         SELECT 'NOT_ASSIGNED' AS DESCRIPTION,
                Count(*)       TYPE_COUNT
         FROM   YOUR_TABLE
         WHERE  TYPE IS NULL)
SELECT *
FROM   CTE_COUNT 

Answer 3

希望，这会有所帮助。

SELECT ISNULL(D.descr, 'Not_Assigned'),
       T2.qty
FROM
    (SELECT T.type,
            COUNT(*) as qty
    FROM Table AS T
    GROUP BY type) AS T2
    LEFT JOIN (SELECT 1 as type, 'Bad' AS descr
               UNION ALL
               SELECT 2, 'Good'
               UNION ALL
               SELECT 3, 'Excellent') AS D ON D.type = T2.type

Answer 4

如果您使用Sql server 2012+，请使用此

SELECT
    [Description] = coalesce(choose (t.[type],'Bad','Good' ,'Excellent'), 'Not_Assigned'),
     t.[Count]
FROM (
    SELECT [type], [Count] = COUNT(*)
    FROM yourtable
    GROUP BY [type]
) t