Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to do the case when the statement is wrong. The introduction in the article is very detailed and has certain reference value. Interested friends must read it!

mysql operates on a syntax:

case .. when statement

But recently encountered a problem when using, the following words do not say much, to see the detailed introduction

That's a direct statement. 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 according to the penultimate digit of this ID number to display the gender information, I follow the following SQL statement execution, the result reported the corresponding error:

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)

Error: operand should contain one column. If there are too many values in parentheses after the when clause, can only one value appear after the when clause under this case when structure? Checking the case syntax in section 13.6.5.1 of the following official documentation, it seems that there is no explanation for this.

Of course, changing the format of the statement can still 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 in the format "CASE value WHEN compare value," the compare value following the when clause can only be a single value, not multiple values. For example, the above comparison value has values of 1, 3, 5, 7, 9. In this case, only the above SQL can be used.

The above is all the contents of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.