MySQL in locate and case when ... Then ... ELSE ... End combined usage

Before the project needs to write a SQL, is to find out a research details, choose a answer, b answer, c answer ... f The answer to how many people each, this SQL is also a great effort to write, it is written down, convenient for later use.

Among them Tbl_research_item is the research detail table, Tbl_user_research_item for user answer detail table

SELECTItem.research_id,item.item_id,item.question,item.question_type,item.option1,item.option2,item.option3, Item.option4,item.option5,item.option6,sum( Case  when(LOCATE ('A', uitem.choose_option)> 0) Then 1 ELSE 0 END) asoption1anscnt,sum( Case  when(LOCATE ('B', uitem.choose_option)> 0) Then 1 ELSE 0 END) asoption2anscnt,sum( Case  when(LOCATE ('C', uitem.choose_option)> 0) Then 1 ELSE 0 END) asoption3anscnt,sum( Case  when(LOCATE ('D', uitem.choose_option)> 0) Then 1 ELSE 0 END) asoption4anscnt,sum( Case  when(LOCATE ('E', uitem.choose_option)> 0) Then 1 ELSE 0 END) asoption5anscnt,sum( Case  when(LOCATE ('F', uitem.choose_option)> 0) Then 1 ELSE 0 END) asoption6anscnt fromTbl_research_item Item Left JOINTbl_user_research_item Uitem on(item.research_id=uitem.research_id anditem.item_id=uitem.item_id)WHEREitem.research_id= '10093' andQuestion_type!= 'input'GROUP  byitem.item_id

Since each research topic is likely to be multi-selected, that is, the value of uitem.choose_option may be AB, so, the idea of this SQL is when the first ' a ' position in Uitem.choose_option is greater than 0, it means that a is selected, that is, recorded as 1 , otherwise 0, and then all the users selected a sum of the number, so get the study of this to the title of the total number of option1anscnt, and so on.

Here by the way, I'll introduce you to the locate and case when you encounter in SQL ... Then ... ELSE ... End two use of functions:

1.LOCATE (SUBSTR,STR): Returns the position of the substring substr the first occurrence in the string str. If the substring substr does not exist in STR, the return value is 0

For example: Select LOCATE (' d ', ' ASDFGHJ ')--->3

2.CASE when ... Then ... ELSE ... END

For example:

SELECT
Case-------------If
When the sex= ' 1 ' then ' Male '-------------sex= ' 1 ', the return value ' male '
When the sex= ' 2 ' then ' female '-------------sex= ' 2 ', the return value ' female '
Else ' other '-------------other return ' other '
End-------------Ended

There is another way of writing this function:

Case sex while '         1 ' then ' Male ' when         ' 2 ' then ' women ' else ' other ' END

Both of these are possible.

MySQL in locate and case when ... Then ... ELSE ... End combined usage