The practice of database row and column transformation caused by a face test

Problem Scenario

Recently a friend went to an interview and asked me a question. The title is as follows, in the form of database table score, to find out that each score (grade) is greater than or equal to 80 students name.

---------------------------------------- name | course  | grade ---------------------------------------- zhangsan  | Java　  | 70 ---------------------------------------- zhangsan  | C++　   | 80 ---------------------------------------- lisi 　   | Java　  | 90 ---------------------------------------- lisi 　   | C++ 　  | 60 ---------------------------------------- wangwu　  | Java　  | 85 ---------------------------------------- wangwu　  | C++　　 | 95 ---------------------------------------- Expected results ---------------------------------------- name ---------------------------------------- wangwu ---------------------------------------- This article takes the MySQL database As an example, realizes the face question request step by step in three kinds of schemes. programme I1, to seek the ranks of the conversion, also known as Matrix Transpose, the multi-column data into a row, this step is the most critical, the implementation of SQL statements as follows:the following SQL is the Name field of a table that is constructed from a table of tables for each course of data table, using the Name field of the main table to construct multiple columns with score as the primary table.

SelectS. ' Name ' asname, (SelectGrade fromScorewhereName=S. ' Name ' andCourse= 'Java') as 'Java',(SelectGrade fromScorewhereName=S. ' Name ' andCourse= 'C + +') as 'C + +' fromScore S

Operation Result:

 2, for 1 of the execution results, you need to filter out the duplicate lines, as long as the keyword through the distinct can be, the implementation of SQL statements as follows:

Select distinctS. ' Name ' asname, #此处加distinct来过滤相同的行 (SelectGrade fromScorewhereName=S. ' Name ' andCourse= 'Java') as 'Java',(SelectGrade fromScorewhereName=S. ' Name ' andCourse= 'C + +') as 'C + +' fromScore S

Operation Result:

3, finally through the construction of a sub-query, that is, the above 2 query results as a table to query, where row-level filtering is possible, the implementation of the SQL statement as follows:

Select * from(    Select distinctS. ' Name ' asname, #此处加distinct来过滤相同的行 (SelectGrade fromScorewhereName=S. ' Name ' andCourse= 'Java') asG1, (SelectGrade fromScorewhereName=S. ' Name ' andCourse= 'C + +') asG2 fromscore s) scorewhereG1>= the  andG2>= the

Operation Result:

question : Here is a question that, if written in the following SQL statement, Java and C + + as the column name (and C #), the query result is null, the question is followed by a detailed explanation, see the results of the operation:

Select * from(    Select distinctS. ' Name ' asname, #此处加distinct来过滤相同的行 (SelectGrade fromScorewhereName=S. ' Name ' andCourse= 'Java') as 'Java',    (SelectGrade fromScorewhereName=S. ' Name ' andCourse= 'C + +') as 'C + +'     fromscore s) scorewhere 'Java'>= the  and 'C + +'>= the

Operation Result:

Programme II

1, through the combination of group and aggregate function sum, through the group by name grouping, using sum pseudo-modulus pseudo-sample to calculate the results of each course, sum when using case to determine the course category, the line shows each student each course performance, this step is the most critical, Implement the SQL statement as follows:

Selectname,sum( Case  whenCourse='Java'  ThenGradeEnd) as 'Java',sum( Case  whenCourse='C + +'  ThenGradeEnd) as 'C + +' fromScoreGroup  byName

Operation Result:

2, then through the construction of a subquery, that is, the above 1 query results as a table to query, where row-level filtering is possible, the implementation of the SQL statement is as follows:

Select * from(Selectname,sum( Case  whenCourse='Java'  ThenGradeEnd) asG1,sum( Case  whenCourse='C + +'  ThenGradeEnd) asG2 fromScoreGroup  byname) scorewhereG1>= the  andG2>= the

Operation Result:

Programme III

1, first find out any course <80 students, the implementation of SQL statements are as follows:

Select   namefrom  scorewhere grade<

Operation Result:

2, distinct out all students list (do not repeat), the implementation of SQL statements are as follows:

Select distinct name  from Score

Operation Result:

3, by constructing a subquery from the results of query 2 to exclude the results of query 1, this step some of the database is a collection function, such as SQL Server except, here we use NOT exists row-level filtering, implementation of SQL statement as follows:

Select * from(    Select distinctname fromscore) Score1where  not exists(    Select *     from    (        Select distinctname fromscorewhereGrade< the) Score2whereScore1.name=score2.name)

Operation Result:

Summarize:

The practice of database row and column transformation caused by a face test