How to use mysql case when?

mysql case when usage:

MySQL has two syntaxes for case when:

Simple function

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

Search function

CASE WHEN [expr] THEN [result1]…ELSE [default] END

What is the difference between these two syntaxes?

1. Simple function

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END: Enumerate this All possible values ​​of the field.

SELECT
    NAME '英雄',
    CASE NAME
        WHEN '德莱文' THEN
            '斧子'
        WHEN '德玛西亚-盖伦' THEN
            '大宝剑'
        WHEN '暗夜猎手-VN' THEN
            '弩'
        ELSE
            '无'
    END '装备'
FROM
    user_info;
    SELECT

    NAME '英雄',
    CASE NAME
        WHEN '德莱文' THEN
            '斧子'
        WHEN '德玛西亚-盖伦' THEN
            '大宝剑'
        WHEN '暗夜猎手-VN' THEN
            '弩'
        ELSE
            '无'
    END '装备'
FROM
    user_info;

2. Search function

CASE WHEN [expr] THEN [result1]…ELSE [default] END: The search function can write judgments , and the search function will only return the first qualified value, and other cases will be ignored

# when 表达式中可以使用 and 连接条件
SELECT
    NAME '英雄',
    age '年龄',
    CASE
        WHEN age < 18 THEN
            &#39;少年&#39;
        WHEN age < 30 THEN
            &#39;青年&#39;
        WHEN age >= 30
        AND age < 50 THEN
            &#39;中年&#39;
        ELSE
            &#39;老年&#39;
    END &#39;状态&#39;
FROM
    user_info;

Recommended tutorial: "mysql video tutorial"

The above is the detailed content of How to use mysql case when?. For more information, please follow other related articles on the PHP Chinese website!