mysql中LOCATE和CASE WHEN...THEN...ELSE...END結合用法

標籤：思路   case   sum   sel   div   多選   select   否則   一個   

之前項目中需要寫一個sql,就是查出某個調研詳情中，選A答案，B答案，C答案...F答案的人各有多少人，這個sql也是費了很大的力氣才寫出來，故記下來，方便以後使用。

其中tbl_research_item為調研詳情表，tbl_user_research_item為使用者答題詳情表

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

由於每道調研的題目有可能為多選，也就是說，uitem.choose_option的值有可能為AB這樣的，所以呢，這個sql的思路是當uitem.choose_option中先‘A’的位置大於0，就表示選了A,也就是記為1，否則為0，再將所有使用者選了A的數量求和，因此得到了這個調研中這到題選A的總人數option1AnsCnt，其他的以此類推。

在這裡順便介紹一下sql中遇到的LOCATE和CASE WHEN...THEN...ELSE...END兩個函數的用法：

1.LOCATE(substr,str) ：返回子串 substr 在字串 str 中第一次出現的位置。如果子串 substr 在 str 中不存在，傳回值為 0

例如：select LOCATE(‘d‘, ‘asdfghj‘)    --->3

2.CASE WHEN...THEN...ELSE...END

例如：

SELECT            
    case                   -------------如果
    when sex=‘1‘ then ‘男‘ -------------sex=‘1‘，則傳回值‘男‘
    when sex=‘2‘ then ‘女‘ -------------sex=‘2‘，則傳回值‘女‘  
    else ‘其他‘            -------------其他的返回‘其他’
    end                   -------------結束

這個函數還有另外一種寫法：

CASE sex         WHEN ‘1‘ THEN ‘男‘         WHEN ‘2‘ THEN ‘女‘ELSE ‘其他‘ END

這兩種寫法都是可以的

mysql中LOCATE和CASE WHEN...THEN...ELSE...END結合用法