Как присвоить несколько значений в операторе CASE?

Мне нужно присвоить два значения моему выбору на основе оператора CASE. В псевдо:

select
    userid
  , case
        when name in ('A', 'B') then 'Apple'
        when name in ('C', 'D') then 'Pear'
    end as snack
from
    table
;

Я присваиваю значение для snack. Но допустим, я также хочу присвоить значение другой переменной, drink, на основе тех же условий. Одним из способов было бы повторить вышеизложенное:

select
    userid
  , case
        when name in ('A', 'B') then 'Apple'
        when name in ('C', 'D') then 'Pear'
    end as snack
  , case
        when name in ('A', 'B') then 'Milk'
        when name in ('C', 'D') then 'Cola'
    end as drink
from
    table
;

Однако, если мне нужно назначить больше значений на основе тех же условий, скажем, food, drink, room и т. д., этот код становится трудно поддерживать.

Есть ли лучший способ сделать это? Могу ли я поместить это в функцию SQL, как вы обычно делаете на другом (сценарном) языке, и если да, не могли бы вы объяснить, как это сделать?


sql sql-server case-statement
person Pr0no    schedule 10.03.2016    source источник
comment
Использование функций улучшает читабельность и удобство сопровождения, но ухудшает производительность.   -  person Tim Schmelter    schedule 10.03.2016
comment
создайте категорию таблицы (Categoryname, FoodName), используйте соединение с исходной таблицей   -  person Ganesh_Devlekar    schedule 10.03.2016
comment
В этом случае я ценю ремонтопригодность и удобочитаемость, а не производительность, так как этот скрипт нужно будет запускать только один раз в день, ночью. Я ожидаю никаких проблем.   -  person Pr0no    schedule 10.03.2016
comment
Это case-выражения, а не case-операторы.   -  person jarlh    schedule 10.03.2016

Ответы (4)


arrow_upward
2
arrow_downward

Функции разрушают производительность. Но вы можете использовать выражение общей таблицы (cte):

with cte as
(
    Select IsNameInList1 = case when name in ('A', 'B') 
                           then 1 else 0 end,
           IsNameInList2 = case when name in ('C', 'D') 
                           then 1 else 0 end,
           t.*
    from table
)
select
    userid
  , case when IsNameInList1=1 then 'Apple'
         when IsNameInList2=1 then 'Pear'
    end as snack
  , case when IsNameInList1=1 then 'Milk'
         when IsNameInList2=1 then 'Cola'
    end as drink
from
    cte
;

На этом пути у вас есть только одно место для обслуживания.

Если производительность запроса не имеет значения, и вы хотите использовать скалярную функцию, подобную этой:

CREATE FUNCTION [dbo].[IsNameInList1] 
(
    @name varchar(100)
)
RETURNS bit
AS
BEGIN
DECLARE @isNameInList bit

BEGIN
    SET @isNameInList =  
    CASE WHEN @name in ('A', 'B')
    THEN 1
    ELSE 0 
    END
END

 RETURN @isNameInList 
END

Затем вы можете использовать его в своем запросе следующим образом:

select
    userid
  , case when dbo.IsNameInList1(name) = 1 then 'Apple'
         when dbo.IsNameInList2(name) = 1 then 'Pear'
    end as snack
from
    table
;

Но более эффективным подходом было бы использование реальной таблицы для их хранения.

person Tim Schmelter    schedule 10.03.2016
comment
И если мне понадобится та же информация в более позднем запросе, не будет ли функция лучше? - person Pr0no; 10.03.2016
comment
@Pr0no: тогда вам следует подумать о том, чтобы сохранить его в реальной таблице. Функция не подходит в большинстве случаев. Только если производительность запроса не имеет значения, это вариант. Это черная дыра для оптимизатора. - person Tim Schmelter; 10.03.2016
comment
Производительность запроса в этом случае не имеет значения. Не могли бы вы показать мне, как будет выглядеть такая функция? Это первый раз, когда я буду использовать функцию. Считая это также возможностью обучения. - person Pr0no; 10.03.2016
comment
Спасибо, Тим, очень признателен - person Pr0no; 10.03.2016

arrow_upward
4
arrow_downward

При выполнении подобных действий я обычно использую соединение с конструктором с табличным значением:

SELECT  t.UserID,
        s.Snack,
        s.Drink
FROM    Table AS T
        LEFT JOIN
        (VALUES
            (1, 'Apple', 'Milk'),
            (2, 'Pear', 'Cola')
        ) AS s (Condition, Snack, Drink)
            ON s.Condition = CASE 
                                WHEN t.name IN ('A', 'B') THEN 1
                                WHEN t.name IN ('C', 'D') THEN 2
                            END;

Я считаю это наиболее гибким, если мне нужно добавить дополнительные условия или столбцы.

Или более подробный, но и более гибкий:

SELECT  t.UserID,
        s.Snack,
        s.Drink
FROM    Table AS T
        LEFT JOIN
        (VALUES
            ('A', 'Apple', 'Milk'),
            ('B', 'Apple', 'Milk'),
            ('C', 'Pear', 'Cola'),
            ('D', 'Pear', 'Cola')
        ) AS s (Name, Snack, Drink)
            ON s.Name= t.name;
person GarethD    schedule 10.03.2016

arrow_upward
1
arrow_downward

Надеюсь, что это поможет вам

SELECT userid
        ,(CASE flag WHEN 1 THEN 'Apple' WHEN 2 THEN 'Pear' WHEN 3 THEN '..etc' END ) as snack
        ,(CASE flag WHEN 1 THEN 'Milk'  WHEN 2 THEN 'Cola' WHEN 3 THEN '..etc' END ) as drink
FROM    (
    SELECT userid
            ,(  CASE    WHEN name IN ('A', 'B') THEN 1 
                    WHEN name IN ('C', 'D') THEN 2 
                    WHEN name IN ('X', 'Y') THEN 3
                ELSE 0 END )    AS flag
    FROM table ) t
person Abdul Rasheed    schedule 10.03.2016

arrow_upward
0
arrow_downward

Как предложил Ганеш в комментарии, я рекомендую создать для этого таблицу сопоставления и просто выполнять поиск. Гораздо проще интерпретировать, поддерживать и масштабировать — и все это с более высокой производительностью.

person alexherm    schedule 04.10.2019