Detailed explanation of the error problem of case when statement

Preface

In the MySQL database, sometimes we use judgment operations like if else. So how to handle this requirement in MySQL?

mysql judgment operation syntax:

case ..when statement

But I encountered a problem when using it recently. I won’t say much below. Let’s take a look at the detailed introduction.

State the problem directly. I have a table id_card_message that stores ID numbers. The table structure and data are as follows (MySQL 5.7.14):

mysql> select * from id_card_message;

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

| id | id_card_no |

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

| 1 | 342513199411222515 |

| 1 | 342624197812023498 |

| 1 | 310908198910123348 |

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

Now I want to display the gender information based on the second to last digit of the ID card number. I execute 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 '男'

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

-> id_card_no

-> from id_card_message;

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

The error says: Operand should contain one column. The only suspicion here is that there are too many values ​​in the parentheses after the when clause. So in this case when structure, can only one value appear after the when clause? I checked the case syntax in section 13.6.5.1 of the following official documents, and it seems that there is no explanation for this.

Of course, you can still get the required data by changing the statement format. as follows:

mysql> select

-> case

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

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

-> id_card_no

-> from id_card_message;

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

| sex | id_card_no |

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

| Male | 342623199610222515 |

| Male | 342624197812023498 |

| Female | 310908198910123348 |

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

The current idea is that, in the format of "CASE value WHEN compare value", the compare value after the when clause can only be a single value and cannot be connected to multiple values. For example, the compare value above has values ​​of 1, 3, 5, 7, and 9. In this case, only the above SQL can be used.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.