mysql中的locate_mysql中LOCATE和CASE WHEN...THEN...ELSE...END结合用法

阳光穿透心脏的1/2处 2023-01-11 10:59 202阅读 0赞

之前项目中需要写一个sql,就是查出某个调研详情中，选A答案，B答案，C答案...F答案的人各有多少人，这个sql也是费了很大的力气才写出来，故记下来，方便以后使用。

其中tbl\_research\_item为调研详情表，tbl\_user\_research\_item为用户答题详情表

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) asoption6AnsCntFROMtbl\_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                   -------------结束

这个函数还有另外一种写法：