MySql CASE WHEN THEN 也需要 WHERE？

Question

我写了这个查询:

SELECT cola, colb, colc, CASE colb
WHEN '6kHcnevOJOSU' THEN 0
WHEN 'g45ujP0td6nw' THEN 1
WHEN 'v83f15lALyFs' THEN 2
END AS sor FROM mytable

'sor' 列正确返回，问题是查询返回所有行，而不仅仅是其中一种情况与 colb 匹配的行!我怎样才能让它只返回匹配的行？

我需要添加 WHERE 吗？但是我总是在没有它的情况下看到这种语法。谢谢

Answer 1

只需添加一个where 子句:

SELECT cola, colb, colc,
       (CASE colb WHEN '6kHcnevOJOSU' THEN 0 
                  WHEN 'g45ujP0td6nw' THEN 1
                  WHEN 'v83f15lALyFs' THEN 2
        END) as sor
FROM mytable t
WHERE colc IN (0, 1, 2);

或者，您可以使用 having 子句:

SELECT cola, colb, colc,
       (CASE colb WHEN '6kHcnevOJOSU' THEN 0 
                  WHEN 'g45ujP0td6nw' THEN 1
                  WHEN 'v83f15lALyFs' THEN 2
        END) as sor
FROM mytable t
HAVING sor IS NOT NULL;