Report statistics (SQL interview questions)

This article from: http://www.cnblogs.com/xiaopin/archive/2010/08/20/1804699.html

There are three tables:

Student table: S

Field: Student ID, Student name

 

Course schedule: c

Field: course No. Course name

 

Orders table: SC

Field: Student ID, course number, score

 

The final result is as follows:

(Some courses are omitted later)

 

Implementation Method:

Method 1:

Selectmax (S. [name]) as name, max (casewhen SC. cid = '1' then SC. score end) as English, max (casewhen SC. cid = '2' then SC. score end) as Chinese, max (casewhen SC. cid = '3' then SC. score end) as history --... of course, the number of such items can be as follows, but SC. the Cid value must be consistent with the course ID in table C, and the alias must also be consistent with the course name from SC, s where SC. SID = S. id groupby SC. sid go

 

The running result is shown in the previous figure.

Advantage: This method is a bit silly and easy to understand.

Disadvantage: all are fixed. If the demand changes, you need to change the code.

 

Method 2: Declare @ sqlvarchar (8000) set @ SQL = ''select @ SQL = @ SQL + ', max (case when SC. cid = ''' + convert (varchar, SC. CID) + ''' then SC. score end) ['+ convert (varchar, C. [name]) + ']' from SC, C where SC. cid = C. id groupby SC. CID, C. [name] Set @ SQL = stuff (@ SQL, 1, 1, '') print @ sqlexec ('select max (S. [name]) as [name], '+ @ SQL +' from SC, s where SC. SID = S. id group by SC. sid ')

 

Running result:

Here we find that the score of chemistry is not? The reason is that there is no score record for chemistry in the transcript.

In this way, a dynamic REPORT query is realized.