Statistics recently due to the development needs, involved in the score statistics this piece, which designed to the application of SQL crosstab, tried to try, feel very feel, now write out everyone look, what is wrong welcome to correct the ^_^
First we set up the following table:
SC (score sheet)
Stuid CLSID Scroe
----- -------- -----------------------------------------------------
0101 1 75.0
0102 1 70.0
0103 1 90.0
0101 2 89.0
0102 2 80.0
0103 2 99.0
0101 3 89.0
0102 3 79.0
0103 3 67.0
Where Stuid represents the student number, the CLSID represents the course number, and Scroe indicates the result
Stu (Student form)
Stuid Stuname
----------- --------------------------------------------------
101 Sheets of Three
102 Dick
103 Harry
CLS (curriculum)
CLSID Name
----------- --------------------------------------------------
1 languages
2 Mathematics
3 English
The next point is the key, in fact, is not very complex, is the use of dynamic SQL
declare @sql nvarchar (4000), @sql1 nvarchar (4000)
Select @sql = ', @sql1 = '
Select @sql = @sql + ', [' +name+ ']=sum (case CLSID] ' +clsid+ ' then Scroe else 0 end) ',
@sql1 = @sql1 + ', [' +name+ ' Rank]= (select SUM (1) from # WHERE [' +name+ ']>=a.[' +name+ ']) '
From (SELECT DISTINCT B.clsid,c.name to SC as b inner join CLS as C on C.clsid=b.clsid) as an order by CLSID
EXEC (' Select Stuid ' + @sql + ', total score =sum (Scroe)
, average points =convert (Dec (5,1), avg (Scroe)), Total rank = (select sum (1) from (select Stuid,aa=sum (Scroe) from SC Group by stuid) AA where sum (A.scroe) <=AA) into # to SC as a group by Stuid select B.stuname as name, a.* ' + @sql1 + ' from # as a inner join Stu as B o N A. Learn number =b.stuid ')
Here's the result:
Name Study number Chinese mathematics English total score average score Chinese rank maths rank English rank
---------------------------------------------------- --------------------------------------
Zhang 30,101 75.0 89.0 89.0 253.0 84.3 2 2 2 1
Lee 40,102 70.0 80.0 79.0 229.0 76.3 3 3 3 2
Wang 50,103 90.0 99.0 67.0 256.0 85.3 1 1 1 3
This is the use of crosstab, which is useful for reporting statistics, so we can write more powerful queries and so on.
