According to the answer situation of the employee's answer activity, the error rate of the questions is counted. The database has the employee's answer sheet employee's question

employee_question

And standard answer sheet

question_info

, you can see that both the standard answer and the answer situation are strings separated by commas

SELECT 
    s.id + 1 as 'Title number',
    COUNT(*) as 'Participation times',
    COUNT(if(e.answer != s.answer, 1, null)) as 'Error times',
    CONCAT(ROUND(COUNT(if(e.answer != s.answer, 1, null))/COUNT(*)*100,1),'','%') as 'error rate'
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

Effect achieved

results of enforcement

Here is a summary of the knowledge points involved in sql

LENGTH(str)

REPLACE(str,substring,replacement)

LENGTH(`standard_answer`)-LENGTH(REPLACE(`standard_answer`, ',', ''))+1  #Count the number of questions

SUBSTRING_INDEX(str,delim,count)

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

Help topic is a self-contained table with increasing fields in mysql database, which can reach 585 at most. With the help topic ID of this table, the answer of cyclic interception is realized, and the number of cyclic interception is limited by where condition

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

Split standard answer