preface

In the interview will often ask the database row turn column problem, in fact, in the development of a lot of projects, especially some financial statements, this is actually a test of SQL query master degree, this chapter we discuss a downward turn column.

What is a row run column

Raw data of data:

Student Number and Name Course Score 001 Three Languages 60 001 Three mathematics 89 001 Three English 88 002 Four Languages 88 002 Four Mathematics 66 002 Four English 90Copy the code

The effect after conversion:

Student Number Name Chinese Math English 001 Zhang SAN 60 89 88 002 Li Si 88 66 90Copy the code

The techniques required for row turn columns

1) Connection query

Join multiple tables together using inner join or left JoinCopy the code

2) Group query

Use the Group by clause to group specific columns, such as the student number in the example aboveCopy the code

3) Aggregation function

Use the Max function to get valued dataCopy the code

4) CASE statement

CASE statements can be embedded in SQL statements for conditional syntax: ELSE result END or CASE column WHEN value THEN result WHEN value THEN result WHEN value THEN result Results the ELSE ENDCopy the code

Implementation of row to column

Table structure:

drop table if exists student; create table student( stu_id int primary key auto_increment, stu_name varchar(20), stu_gender varchar(20), stu_age int ); drop table if exists course; create table course( course_id int primary key auto_increment, course_name varchar(20) ); drop table if exists score; create table score( score_id int primary key auto_increment, stu_id int, course_id int, score int, constraint fk_stu_id foreign key (stu_id) references student(stu_id), constraint fk_course_id foreign key (course_id) references course(course_id) ); Insert into student (stu_name stu_gender, stu_age) values (' zhang 'and' male ', 15), (' bill 'and' male ', 15), (' Cathy 'and' male ', 15), (' zhao six 'and' male ', 15); Insert into course (course_name) values (' Chinese '), (' mathematics'), (" English "); Insert into score (stu_id, course_id, score) values (1,1,80),,2,82 (1), (1,3,84),,1,60 (2), ,2,70 (2), (2,3,86),,1,83 (3), (3,2,77), (3,3,89);Copy the code
  1. Join all tables with a left join query
Select s.stu_id 'id ',s.stu_name' 名', sc.score 'score' from student s left join score sc on s.stu_id = sc.stu_id left join course c on c.course_id = sc.course_id; Query result: Student Number Name Chinese Math English 2 Xiao zhou Chinese 60 2 xiao zhou math 70 2 Xiao Zhou English 86 1 xiao Zhang Chinese 80 1 Xiao Zhang math 82 1 Xiao Zhang English 84 3 Wang Wu Chinese 83 3 Wang Wu math 77 3 Wang Wu English 89 4 Zhao sixCopy the code
  1. Convert each course to a column using a case statement
Select s.tu_id 'student id ', s.tu_name' name ', Elseif case c.course_name when 'c' then sc.score else 0 end 'C ', Case C.course_name when 'math' then SC. score else 0 end 'math ', Case c.course_name when 'English' then sc.score else 0 end 'English' from student s left join score sc on s.stu_id = sc.stu_id left join course c on c.course_id = sc.course_id; Query result: Student Number Name Chinese Math English 2 Xiao zhou 60 0 0 2 Xiao zhou 0 70 0 2 Xiao Zhou 0 0 86 1 xiao Zhang 80 0 0 1 Xiao Zhang 0 82 0 1 Xiao Zhang 0 0 84 3 Wang Wu 83 0 0 3 Wang Five 0 77 0 3 Wang Five 0 0 89 4 Zhao Liu 0 0 0Copy the code
  1. Group by student number
Select s.stu_id 'iD ',s.stu_name' id ', (case c.course_name when 'id' then sc.score else 0 end) 'id ', (case C.course_name when 'math' then SC. score else 0 end) 'math ', (case c.course_name when 'English' then SC. score else 0 end) 'English' from student s left join score sc on s.stu_id = sc.stu_id left join course c on c.course_id = sc.course_id group by s.stu_id; Query result: Student Id name Chinese Math English 1 Xiao Zhang 80 0 2 Xiao Zhou 60 0 3 Wang Wu 83 0 4 Zhao 60 0 0Copy the code
  1. Use Max and Sum to calculate scores greater than 0 for each course
Select s.stu_id 'iD ',s.stu_name' id ', Max (case c.course_name when 'id' then SC. score else 0 end) 'id ', Max (case c.course_name when 'math' then SC. score else 0 end) 'math ', Max (case c.course_name when 'English' then SC. score else 0 end) 'English' from student s left join score sc on s.stu_id = sc.stu_id left join course c on c.course_id = sc.course_id group by s.stu_id; Query result: Student Id name Chinese Math English 1 Xiao Zhang 80 82 84 2 Xiao Zhou 60 70 86 3 Wang Wu 83 77 89 4 Zhao 60 0 0Copy the code

conclusion

The above is a common way to implement row to column, if you are helpful, click a “like” on the bottom left corner 🙂