統計分數

SELECT CourseId,CourseName,
CASE WHEN (Score<10) THEN 0
WHEN (10<=Score AND Score<20) THEN 10
WHEN (20<=Score AND Score<30) THEN 20
WHEN (30<=Score AND Score<40) THEN 30
WHEN (40<=Score AND Score<50) THEN 40
WHEN (50<=Score AND Score<60) THEN 50
WHEN (60<=Score AND Score<70) THEN 60
WHEN (70<=Score AND Score<80) THEN 70
WHEN (80<=Score AND Score<90) THEN 80
WHEN (90<=Score AND Score<100) THEN 90
WHEN (Score=100) THEN 100
END AS Score
INTO #temp
FROM v_exam

SELECT CourseId,CourseName,Score,count(Score) AS ScoreCount FROM #temp
GROUP BY CourseId,CourseName,Score

DROP TABLE #temp




13    BBC    0    1
14    VOA    40    1
15    10萬個為什麼    0    1
18    企業應用模式    0    1
18    企業應用模式    50    1
20    企業管理    40    1
22    AJAX入門    0    1
23    C#高級編程    0    2
23    C#高級編程    30    1
24    C#基礎編程    0    1
24    C#基礎編程    40    2
30    SPC 訓練教材    0    2
30    SPC 訓練教材    40    1
32    時間管理講義    20    1