background
As required by the company, it is now necessary to make statistics on the data of the questionnaire filled in by the user. The final presentation style is EXCEL, and the content is the options of each question filled in by the user, that is, the following table style:
| User ID number | Question 1 | Question 2 | Question 3 | ... | Question 22 |
|---|---|---|---|---|---|
| 110000190001010011 | 1 | 2 | 1,4 | ... | 1 |
| 370000199002021002 | 3 | 1 | 2,5 | ... | 1,3,4 |
Let's introduce the related tables first:
1. Questionnaire: topic_ Question (save all the questionnaire questions, which are not used in this case, but only mention too many display fields)
2. Questionnaire options: topic_ Option (option to save all questions)
| Field name | Field meaning |
|---|---|
| id | Auto increment primary key |
| question_id | Question id |
| option | Option text description |
| order | Option number for each question |
| use_flag | Start |
- Questionnaire user filling form: topic_answer (save the filling status of all users. An option for a question filled in by a user is a row of data)
| Field name | Field meaning |
|---|---|
| id | Auto increment primary key |
| question_id | Question id(topic_question.id) |
| option_id | Option ID table primary key (topic_option.id is not the option ID of the corresponding problem) |
| user_id | User table id |
| task_id | Task id |
- User table (user_info)
| Field name | Field meaning |
|---|---|
| id | Auto increment primary key |
| id_card | ID number |
sql writing logic:
- Because the final output is displayed on the id card number, the corresponding id number should be querying according to the id number id.
- Because multiple questions are selected, to display multiple options in the same cell, you need to merge the options that users fill in the same question first;
- Because topic_ The answer table stores a row of data as an option for a user's question, so all the question options filled in by the user need to be row to column according to the question number;
- Because topic_ Option of answer table_ The ID field is not the option serial number, so you need to perform a joint query to find the corresponding option serial number;
Therefore, the current problems:
- How to merge the options of the same question of the same user, that is, the merged row data mentioned in the title;
- How to display the same problem of multiple users horizontally: each user is a row of data, except the first column is the ID number, and the remaining column is the option of each question, that is, the row in the title.
sql preparation:
It's easy to say with the above logical process and problems, so after finding the blog related to the merged line, I wrote the first version of sql. This version of sql can merge the data of multiple lines according to the problem id, that is, merge the data of multiple lines according to the conditions in the Title, and turn multiple lines into one line;
Reference blog: Merge multiple rows of data into one row of data in mysql
SELECT USER.id_card, answer.question_id, GROUP_CONCAT( answer.option_id SEPARATOR ',' ) AS option_id FROM topic_answer answer LEFT JOIN user_info USER ON answer.user_id = USER.id GROUP BY USER.id_card, answer.question_id;
After that, I checked the relevant documents and wrote the second version of sql. This version is basically formed from the target, but there are many pits in the middle, which takes a relatively long time. Later, I will talk about the places that need ideas; However, the row to column conversion is completed, mainly by using GROUP_CONCAT and CASE... WHEN... THEN methods.
Reference blog: mysql row to column, multi row to one row, column to row, one row to multiple columns
SELECT answer2.id_card 'ID card No.', GROUP_CONCAT( CASE answer2.question_id WHEN 1 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 1', GROUP_CONCAT( CASE answer2.question_id WHEN 2 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 2', GROUP_CONCAT( CASE answer2.question_id WHEN 3 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 3', GROUP_CONCAT( CASE answer2.question_id WHEN 4 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 4', GROUP_CONCAT( CASE answer2.question_id WHEN 5 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 5', GROUP_CONCAT( CASE answer2.question_id WHEN 6 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 6', GROUP_CONCAT( CASE answer2.question_id WHEN 7 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 7', GROUP_CONCAT( CASE answer2.question_id WHEN 8 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 8', GROUP_CONCAT( CASE answer2.question_id WHEN 9 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 9', GROUP_CONCAT( CASE answer2.question_id WHEN 10 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 10', GROUP_CONCAT( CASE answer2.question_id WHEN 11 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 11', GROUP_CONCAT( CASE answer2.question_id WHEN 12 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 12', GROUP_CONCAT( CASE answer2.question_id WHEN 13 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 13', GROUP_CONCAT( CASE answer2.question_id WHEN 14 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 14', GROUP_CONCAT( CASE answer2.question_id WHEN 15 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 15', GROUP_CONCAT( CASE answer2.question_id WHEN 16 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 16', GROUP_CONCAT( CASE answer2.question_id WHEN 17 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 17', GROUP_CONCAT( CASE answer2.question_id WHEN 18 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 18', GROUP_CONCAT( CASE answer2.question_id WHEN 19 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 1', GROUP_CONCAT( CASE answer2.question_id WHEN 20 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 20', GROUP_CONCAT( CASE answer2.question_id WHEN 21 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 21', GROUP_CONCAT( CASE answer2.question_id WHEN 22 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 22' FROM ( SELECT DISTINCT(user_info.id_card) AS id_card, answer.question_id AS question_id, GROUP_CONCAT(answer.option_id SEPARATOR ',' ) AS option_id FROM topic_answer answer LEFT JOIN user_info user_info ON answer.user_id = user_info.id GROUP BY user_info.id_card, answer.question_id ) as answer2 GROUP BY answer2.id_card;
Then, you need to integrate the option id into this, so that the page is displayed with the option id corresponding to each question, not the primary key of the option table, so the third version of sql is shown:
SELECT answer2.id_card 'ID card No.', GROUP_CONCAT( CASE answer2.question_id WHEN 1 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 1', GROUP_CONCAT( CASE answer2.question_id WHEN 2 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 2', GROUP_CONCAT( CASE answer2.question_id WHEN 3 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 3', GROUP_CONCAT( CASE answer2.question_id WHEN 4 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 4', GROUP_CONCAT( CASE answer2.question_id WHEN 5 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 5', GROUP_CONCAT( CASE answer2.question_id WHEN 6 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 6', GROUP_CONCAT( CASE answer2.question_id WHEN 7 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 7', GROUP_CONCAT( CASE answer2.question_id WHEN 8 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 8', GROUP_CONCAT( CASE answer2.question_id WHEN 9 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 9', GROUP_CONCAT( CASE answer2.question_id WHEN 10 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 10', GROUP_CONCAT( CASE answer2.question_id WHEN 11 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 11', GROUP_CONCAT( CASE answer2.question_id WHEN 12 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 12', GROUP_CONCAT( CASE answer2.question_id WHEN 13 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 13', GROUP_CONCAT( CASE answer2.question_id WHEN 14 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 14', GROUP_CONCAT( CASE answer2.question_id WHEN 15 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 15', GROUP_CONCAT( CASE answer2.question_id WHEN 16 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 16', GROUP_CONCAT( CASE answer2.question_id WHEN 17 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 17', GROUP_CONCAT( CASE answer2.question_id WHEN 18 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 18', GROUP_CONCAT( CASE answer2.question_id WHEN 19 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 1', GROUP_CONCAT( CASE answer2.question_id WHEN 20 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 20', GROUP_CONCAT( CASE answer2.question_id WHEN 21 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 21', GROUP_CONCAT( CASE answer2.question_id WHEN 22 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 22' FROM ( SELECT DISTINCT(user_info.id_card) AS id_card, answer.question_id AS question_id, GROUP_CONCAT(answer.option_id SEPARATOR ',' ) AS option_id FROM topic_answer answer LEFT JOIN user_info ON answer.user_id = user_info.id LEFT JOIN topic_option on answer.option_id = topic_option.id GROUP BY user_info.id_card, answer.question_id ORDER BY answer.question_id ) as answer2 GROUP BY answer2.id_card;
Finally, improve the results. The first is to sort the option results of each question. The second is that some users fill in it many times, so they need to de duplicate it. The main use is GROUP_CONCAT(distinct XXX order by XXX), because the default is to use commas for segmentation, I will group_ The SEPARATOR in concat has been deleted, and this is the final version;
Reference blog: group_concat sort the functions you don't know and de duplicate them
SELECT answer2.id_card 'ID card No.', GROUP_CONCAT( CASE answer2.question_id WHEN 1 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 1', GROUP_CONCAT( CASE answer2.question_id WHEN 2 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 2', GROUP_CONCAT( CASE answer2.question_id WHEN 3 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 3', GROUP_CONCAT( CASE answer2.question_id WHEN 4 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 4', GROUP_CONCAT( CASE answer2.question_id WHEN 5 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 5', GROUP_CONCAT( CASE answer2.question_id WHEN 6 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 6', GROUP_CONCAT( CASE answer2.question_id WHEN 7 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 7', GROUP_CONCAT( CASE answer2.question_id WHEN 8 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 8', GROUP_CONCAT( CASE answer2.question_id WHEN 9 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 9', GROUP_CONCAT( CASE answer2.question_id WHEN 10 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 10', GROUP_CONCAT( CASE answer2.question_id WHEN 11 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 11', GROUP_CONCAT( CASE answer2.question_id WHEN 12 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 12', GROUP_CONCAT( CASE answer2.question_id WHEN 13 THEN CAST(answer2.option_id AS char) ELSE NULL END ) 'Question 13', GROUP_CONCAT( CASE answer2.question_id WHEN 14 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 14', GROUP_CONCAT( CASE answer2.question_id WHEN 15 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 15', GROUP_CONCAT( CASE answer2.question_id WHEN 16 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 16', GROUP_CONCAT( CASE answer2.question_id WHEN 17 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 17', GROUP_CONCAT( CASE answer2.question_id WHEN 18 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 18', GROUP_CONCAT( CASE answer2.question_id WHEN 19 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 1', GROUP_CONCAT( CASE answer2.question_id WHEN 20 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 20', GROUP_CONCAT( CASE answer2.question_id WHEN 21 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 21', GROUP_CONCAT( CASE answer2.question_id WHEN 22 THEN CAST(answer2.option_id AS char) ELSE NULL END) 'Question 22' FROM ( SELECT DISTINCT(user_info.id_card) AS id_card, answer.question_id AS question_id, GROUP_CONCAT(distinct topic_option.order order by topic_option.order) AS option_id FROM topic_answer answer LEFT JOIN user_info ON answer.user_id = user_info.id LEFT JOIN topic_option on answer.option_id = topic_option.id GROUP BY user_info.id_card, answer.question_id ORDER BY answer.question_id ) as answer2 GROUP BY answer2.id_card;
Finally, summarize the knowledge learned and the pit you stepped on:
The first and most important thing to learn is how to sort out the logic of solving the problem without a clue. This is mainly to disassemble the problem. You can sort out ideas first, solve it step by step, and consult relevant materials. At the same time, the ability of autonomous learning is also very important, Don't just want to ask others for advice WHEN you encounter problems. Think clearly before you start to do it. THEN there is group_ How to use the concat method in combination with CASE... WHEN... THEN, distinct and order;
The pit I stepped on was actually my own carelessness_ The ID field is originally a number, but after multiple rows of data are merged into one row, it becomes a char type, so the outermost layer is changed from SUM(CASE answer2.question_id WHEN 1 THEN answer2.question_id else 0 end) 1 to GROUP_CONCAT (case answer2.question_id when 1 then cast (answer2.option_id as char) else null end) 1. I thought this would be OK, but I kept reporting sql statement errors. It took a long time to locate it and use group_ After concat, it becomes a string, so it cannot be displayed on the title bar with numbers, so it is changed to GROUP_CONCAT (case answer2. Question_id when 1 then cast (answer2. Option_id as char) else null end) 'question 1', special attention should be paid when using.
Well, I'll write this question first, because I haven't written a slightly more complex sql for a long time, and I haven't used group before_ Concat method, so I want to record this time. Of course, it's a great honor if I can help you at the same time. Similarly, if you have any suggestions or optimization places for the final version of sql, please leave your ideas in the comment area ~