Use LOCATE and case when... THEN... ELSE... END in mysql in combination,

Use LOCATE and case when... THEN... ELSE... END in mysql in combination,

In the previous project, you need to write an SQL statement to find out A survey details. Select answer A, Answer B, and answer C... f. The number of people in the answer is different. This SQL statement is written with great effort, so it is easy to use it later.

Here, tbl_research_item is the survey Details table, and tbl_user_research_item is the user's answer Details table.

SELECT item.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) as option1AnsCnt,sum(CASE WHEN (LOCATE('B', uitem.choose_option) > 0) THEN 1 ELSE 0 END) as option2AnsCnt,sum(CASE WHEN (LOCATE('C', uitem.choose_option) > 0) THEN 1 ELSE 0 END) as option3AnsCnt,sum(CASE WHEN (LOCATE('D', uitem.choose_option) > 0) THEN 1 ELSE 0 END) as option4AnsCnt,sum(CASE WHEN (LOCATE('E', uitem.choose_option) > 0) THEN 1 ELSE 0 END) as option5AnsCnt,sum(CASE WHEN (LOCATE('F', uitem.choose_option) > 0) THEN 1 ELSE 0 END) as option6AnsCntFROM tbl_research_item itemLEFT JOIN tbl_user_research_item uitem ON (item.research_id=uitem.research_id and item.item_id=uitem.item_id)WHERE item.research_id = '10093'and question_type != 'input'GROUP BY item.item_id

Because the questions in each survey may be multiple choices, that is, uitem. the value of choose_option may be AB, so the idea of this SQL statement is to use uitem. if the position of 'A' in choose_option is greater than 0, it indicates that A is selected, that is, it is recorded as 1. Otherwise, it is 0. Then, the sum of the number of A selected by all users is obtained, therefore, the total number of candidates for question A in this survey is option1AnsCnt, and so on.

Here, by the way, we will introduce the usage of the LOCATE and case when... THEN... ELSE... END functions in SQL:

1. LOCATE (substr, str): returns the position of the substring substr in the string str for the first time. If the substring substr does not exist in str, the return value is 0.

Example: select LOCATE ('D', 'asdfghj') ---> 3

2. case when... THEN... ELSE... END

For example:

SELECT
Case ------------- if
When sex = '1' then 'male' ------------- sex = '1', the return value is 'male'
When sex = '2' then 'female '------------- sex = '2', the return value is 'femal'
Else 'others' ----------- other return 'others'
End ------------- end

There is another way to write this function:

CASE sex WHEN '1' then' male 'when' 2' then' female 'else' others' END

Both methods are acceptable.