What is the usage of mysql case when

The usage of mysql case when is: 1. Used as a simple search, the syntax is [CASE [col_name] WHEN [value1]]; 2. Used as a search function, the syntax is [CASE WHEN [expr] THEN [ result1]].

【Related learning recommendations: mysql tutorial(Video)】

The usage of mysql case when is:

There are 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 all possible values ​​of this 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 value that meets the conditions, and other cases are 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;

聚合函数 sum 配合 case when 的简单函数实现行转列
SELECT
    st.stu_id '学号',
    st.stu_name '姓名',
    sum(
        CASE co.course_name
        WHEN '大学语文' THEN
            sc.scores
        ELSE
            0
        END
    ) '大学语文',
    sum(
        CASE co.course_name
        WHEN '新视野英语' THEN
            sc.scores
        ELSE
            0
        END
    ) '新视野英语',
    sum(
        CASE co.course_name
        WHEN '离散数学' THEN
            sc.scores
        ELSE
            0
        END
    ) '离散数学',
    sum(
        CASE co.course_name
        WHEN '概率论与数理统计' THEN
            sc.scores
        ELSE
            0
        END
    ) '概率论与数理统计',
    sum(
        CASE co.course_name
        WHEN '线性代数' THEN
            sc.scores
        ELSE
            0
        END
    ) '线性代数',
    sum(
        CASE co.course_name
        WHEN '高等数学' THEN
            sc.scores
        ELSE
            0
        END
    ) '高等数学'
FROM
    edu_student st
LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id
LEFT JOIN edu_courses co ON co.course_no = sc.course_no
GROUP BY
    st.stu_id
ORDER BY
    NULL;

Want to know more For programming learning, please pay attention to the php training column!

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