Detailed Explanation of Error Reporting of case when Statement

Preface

In the mysql database, we sometimes use judgment operations like if else. So how do you deal with this requirement in mysql?

mysql decision operation 1 syntax:

case... when statement

But recently encountered a problem when using, the following words are not much to say, let's take a look at the detailed introduction

Explain the problem directly. I have a table id_card_message for storing ID numbers. The structure and data of the table are as follows (MySQL5.7. 14):

mysql> select * from id_card_message;

+------+--------------------+

| id | id_card_no |

+------+--------------------+

| 1 | 342513199411222515 |

| 1 | 342624197812023498 |

| 1 | 310908198910123348 |

+------+--------------------+

Now, according to the penultimate digit of this ID number, the male and female information is displayed. I follow the following SQL statement, and the corresponding error is reported:

mysql> select case substr(id_card_no,17,1)

-> when (1,3,5,7,9) then ' Male '

-> when (0,2,4,6,8) then ' Female ' end 'sex',

-> id_card_no

-> from id_card_message;

ERROR 1241 (21000): Operand should contain 1 column(s)

The error prompted me that the operand should contain 1 column. Here, we can only suspect that there are too many values in parentheses after when clause. Under this case when structure, can only one value appear after when clause? I checked the following official document for case syntax in section 13.6.5. 1, and it seems that this is not explained.

Of course, if you change the statement format, you can get the required data. As follows:

mysql> select

-> case

-> when substr(id_card_no,17,1) in (1,3,5,7,9) then ' Male '

-> when substr(id_card_no,17,1) in (0,2,4,6,8) then ' Female ' end 'sex',

-> id_card_no

-> from id_card_message;

+------+--------------------+

| sex | id_card_no |

+------+--------------------+

|  Male  | 342623199610222515 |

|  Male  | 342624197812023498 |

|  Female  | 310908198910123348 |

+------+--------------------+

The idea now is that under the format of "CASE value WHEN compare value", compare value after when clause can only be a single value, and cannot be followed by multiple values. For example, the values of compare value above are 1, 3, 5, 7, 9. In this case, only the above SQL can be used.

Summarize