统计答题的正确率（Mysql统计逗号隔开的字符）

针对员工答题活动的答题情况统计题目错误率，数据库中有员工答题表employee_question

employee_question

和标准答案表question_info

question_info

，可以看到，标准答案和答题情况都是用逗号隔开的字符串

SELECT 
    s.id + 1 as '题目编号',
    COUNT(*) as '答题参与次数',
    COUNT(if(e.answer != s.answer, 1, null)) as '错误次数',
    CONCAT(ROUND(COUNT(if(e.answer != s.answer, 1, null))/COUNT(*)*100,1),'','%') as '错误率'
FROM (
    SELECT
            help_topic_id as id, 
            SUBSTRING_INDEX(SUBSTRING_INDEX(answer,',',help_topic_id+1),',',-1) AS answer 
    FROM 
            mysql.help_topic,(SELECT * FROM employee_question GROUP BY employee_num,question_id) a
    WHERE 
            help_topic_id < LENGTH(answer) - LENGTH(REPLACE(answer, ',', ''))+1
    AND question_id = 2
)e LEFT JOIN (
    SELECT
            help_topic_id as id, 
            SUBSTRING_INDEX(SUBSTRING_INDEX(standard_answer,',',help_topic_id+1),',',-1) AS answer 
    FROM 
            mysql.help_topic,question_info
    WHERE 
            help_topic_id < LENGTH(standard_answer) - LENGTH(REPLACE(standard_answer, ',', ''))+1
    AND id = 2
)s ON e.id = s.id
GROUP BY s.id
ORDER BY ROUND(COUNT(if(e.answer != s.answer, 1, null))/COUNT(*)*100,1) DESC

实现的效果

执行结果

下面针对sql中涉及到的知识点做个总结

LENGTH(str)

REPLACE(str,substring,replacement)

LENGTH(`standard_answer`)-LENGTH(REPLACE(`standard_answer`, ',', ''))+1  #计算题目个数

SUBSTRING_INDEX(str,delim,count)

SUBSTRING_INDEX('www.baidu.com', '.', 2)   //www.baidu
SUBSTRING_INDEX('www.baidu.com', '.', -1)  //com

help_topic是mysql数据库自带的一个有递增字段的表，最大可以到585，借助这个表的help_topic_id实现了循环截取answer，并且通过where条件限制了循环截取的次数

SELECT
    help_topic_id as id, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(standard_answer,',',help_topic_id+1),',',-1) AS answer 
FROM 
    mysql.help_topic,question_info
WHERE 
    help_topic_id < LENGTH(standard_answer) - LENGTH(REPLACE(standard_answer, ',', ''))+1
    AND id = 2

分割标准答案