Recently, due to the development needs, it involved the grade statistics. This is designed to the application of the SQL crosstab. I tried it. I feel very feeling. Now I wrote it out, what is wrong, the welcome pointing ^ _ ^
First we build the following table:
SC (transcript) stuid clsid scroe ------------ ----------------------------- ------------------------ 0101 1 75.00102 1 70.00103 1 90.00101 2 89.00102 2 80.00103 2 99.00101 3 89.00102 3 79.00103 3 67.0
STUID means the student number, CLSID represents the course number, SCROE expresses grades.
STU (Student Table) Stuid Stuname -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------ 101 Zhang 102 Li Si 103 Wang Wu
CLS (Course) CLSID NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------ 1 Language 2 Mathematics 3 English
Next is the key, it is not a good complicated, it is used to use dynamic SQL.
Declare @sql nvarchar (4000), @ SQL1 nVARCHAR (4000) SELECT @SQL = ', @ SQL1 =' '
SELECT @ SQL = @ SQL ', [' Name '] = SUM (Case CLSID WHEN' ' CLSID ' '' 'TENSE ELSE 0 End)', @ SQL1 = @ SQL1 ', [' Name 'Name] = (Select Sum (1) from # where [' name ']> = a. [' Name ']) 'from (Select Distinct B.clsid, C.Name from SC AS B INNER JOIN CLS AS C on C .clsid = b.clsid) AS A Order By Clsidexec ('Select Stuid Learn' @ SQL ', total score = SUM (scroe), average = Convert (Dec (5, 1), AVG (Scroe), Total name = (Select Sum (Select Sum (Select Stuid, AA = SUM (SCROE) from scroup by stuid) AA where sum (a.scroe) <= aa) Into # from sc as a group by stuid select b. Stuname as name, a. * ' @ SQL1 ' from # as a inner join stu as b on a. Learn = B.STUID ')
The following is the result:
Names' Language Mathematics English Total Average Points Total Language Name Number Map English Name ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------- -------- Zhang 3 0101 75.0 89.0 89.0 253.0 84.3 2 2 2 1 Li Si 0102 70.0 80.0 79.0 229.0 76.3 3 3 3 2 Wang 50103 90.0 99.0 67.0 256.0 85.3 1 1 1 3
This is the usage of the crosstab. For report statistics, we can write a more powerful query according to this class.
