CODEWITHSUNDEEP
mysqlcase
LF-DevJourney
LF-DevJourney

Reputation: 28529

mysql case statement breaks

I use below case statement to process enumeration in my sql. But it return wrong result.

SELECT  material_name,material_id,grade_id,subject_id,course_term_id,
        CASE grade_id
                WHEN grade_id =  1 THEN "一年级"
                WHEN grade_id =  2 THEN "二年级"
                WHEN grade_id =  3 THEN "三年级"
                WHEN grade_id =  4 THEN "四年级"
                WHEN grade_id =  5 THEN "五年级"
                WHEN grade_id =  6 THEN "六年级"
                WHEN grade_id =  7 THEN "初一"
                WHEN grade_id =  8 THEN "初二"
                WHEN grade_id =  9 THEN "初三"
                WHEN grade_id = 10 THEN "高一"
                WHEN grade_id = 11 THEN "高二"
                WHEN grade_id = 12 THEN "高三"
                ELSE "" END as grade,
        CASE subject_id
                WHEN subject_id =  1 THEN "数学"
                WHEN subject_id =  2 THEN "物理"
                WHEN subject_id =  3 THEN "化学"
                WHEN subject_id =  4 THEN "语文"
                WHEN subject_id =  5 THEN "英语"
                WHEN subject_id =  6 THEN "科学"
                WHEN subject_id =  7 THEN "音乐"
                WHEN subject_id =  8 THEN "绘画"
                WHEN subject_id =  9 THEN "政治"
                WHEN subject_id = 10 THEN "历史"
                WHEN subject_id = 11 THEN "地理"
                WHEN subject_id = 12 THEN "生物"
                WHEN subject_id = 13 THEN "奥数"
                ELSE "" END as subject,
        CASE course_term_id
                WHEN course_term_id = 1 THEN "春"
                WHEN course_term_id = 2 THEN "暑"
                WHEN course_term_id = 3 THEN "秋"
                WHEN course_term_id = 4 THEN "寒"
                ELSE "" END as season,
        created_at, updated_at from sc_materials where material_id in (2025,317,2050);

And the output, where the red mark circle should with value not empty string. enter image description here

When I do as Bill Karwin comment use ELSE COALESCE(grade_id, '<NULL>') END, it shows,

enter image description here

Here is the data type I use with these field,

grade_id    int
subject_id  int 
course_term_id  tinyint

mysql -V
mysql  Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

Upvotes: 1

Views: 463

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562310

Okay I spotted something in your use of CASE. I'm sorry I didn't notice this at first.

CASE has two forms:

CASE <expr1> WHEN <expr2> THEN <result> ...

CASE WHEN <expr3> THEN <result> ...

In the first form, the case is true only if <expr1> = <expr2>.

In the second form, the case is true if <expr3> is true.

Read https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html#operator_case for more details and examples.

But you seem to be trying to do a comparison in <expr2>. Therefore <expr2> will only be 0 or 1, depending on whether the comparison is false or true. Then the 0 or 1 value will be compared to <expr1>, so it will succeed only if <expr1> is 1.

You should change your CASE expression to this:

    CASE grade_id
            WHEN  1 THEN "一年级"
            WHEN  2 THEN "二年级"
            WHEN  3 THEN "三年级"
            WHEN  4 THEN "四年级"
            WHEN  5 THEN "五年级"
            WHEN  6 THEN "六年级"
            WHEN  7 THEN "初一"
            WHEN  8 THEN "初二"
            WHEN  9 THEN "初三"
            WHEN 10 THEN "高一"
            WHEN 11 THEN "高二"
            WHEN 12 THEN "高三"
            ELSE "" END as grade,

And similarly for the other CASE expressions.

But I also agree with the comment from @tadman — you should just use three lookup tables.

Upvotes: 2

Related Questions