這篇盡量用最簡單的範例來展示,所以不可避免的,選上了成績資料為展示的目標,當然實際上運作可能包含更多的欄位,範例則是盡量精簡。一般來說資料庫考慮到各程式語言的相容性、可遷移性,盡量不用中文,範例就不太講究了,因為中文也是一種註解,就偷個懶。
資料表:
清單為記錄所有學生各科的成績
DBID | 姓名 | 科目 | 分數 |
---|---|---|---|
1 | 小明 | 國文 | 95 |
2 | 小華 | 國文 | 100 |
3 | 小英 | 國文 | 87 |
4 | 小明 | 英文 | 99 |
5 | 小華 | 英文 | 94 |
7 | 小明 | 數學 | 91 |
8 | 小華 | 數學 | 96 |
9 | 小英 | 數學 | 89 |
分數級距為老師評鑑成績的名稱:
DBID | 級距 | 上限 | 下限 |
---|---|---|---|
1 | 滿分 | 101 | 100 |
2 | 優 | 100 | 95 |
3 | 須加強 | 95 | 0 |
1. 要取得學生清單
SQL 語法 |
---|
SELECT DISTINCT 清單.姓名 FROM 清單; |
姓名 |
---|
小明 |
小英 |
小華 |
2. 每個學生的平均
SQL 語法 |
---|
SELECT 姓名, AVG(分數) AS 平均 FROM 清單 GROUP BY 姓名; |
姓名 | 平均 |
---|---|
小明 | 95 |
小英 | 88 |
小華 | 96.6666666666667 |
3. 替學生各科成績加上中文評鑑標語:
SQL 語法 |
---|
SELECT 清單.姓名, 清單.科目, 清單.分數, 分數級距.級距 FROM 清單 LEFT JOIN 分數級距 ON 清單.分數<分數級距.上限 AND 清單.分數>=分數級距.下限; |
姓名 | 科目 | 分數 | 級距 |
---|---|---|---|
小明 | 國文 | 95 | 優 |
小華 | 國文 | 100 | 滿分 |
小英 | 國文 | 87 | 須加強 |
小明 | 英文 | 99 | 優 |
小華 | 英文 | 94 | 須加強 |
小明 | 數學 | 91 | 須加強 |
小華 | 數學 | 96 | 優 |
小英 | 數學 | 89 | 須加強 |
4. 各科成績表,不含平均:
SQL 語法 |
---|
SELECT 名冊.姓名, 國文成績表.分數 AS 國文, 英文成績表.分數 AS 英文, 數學成績表.分數 AS 數學 FROM (((名冊 LEFT JOIN 清單 AS 國文成績表 ON (名冊.姓名=國文成績表.姓名 AND 國文成績表.科目=’國文’)) LEFT JOIN 清單 AS 英文成績表 ON (名冊.姓名=英文成績表.姓名 AND 英文成績表.科目=’英文’)) LEFT JOIN 清單 AS 數學成績表 ON (名冊.姓名=數學成績表.姓名 AND 數學成績表.科目=’數學’)) ; |
姓名 | 國文 | 英文 | 數學 |
---|---|---|---|
小明 | 95 | 99 | 91 |
小英 | 87 | 89 | |
小華 | 100 | 94 | 96 |
5. 成績總表,各科成績加上總平均,這裡偷個懶,我將各科成績表、各人平均都建立成 View (檢視),實際上可純靠 SQL 語法兜出來,只是不想讓 SQL 語法太長:
SQL 語法 |
---|
SELECT 各科成績表.*, 各人平均.平均 FROM 各科成績表 LEFT JOIN 各人平均 ON 各科成績表.姓名=各人平均.姓名 ORDER BY 各人平均.平均 DESC; |
姓名 | 國文 | 英文 | 數學 | 平均 |
---|---|---|---|---|
小華 | 100 | 94 | 96 | 96.6666666666667 |
小明 | 95 | 99 | 91 | 95 |
小英 | 87 | 89 | 88 |
6. 最後要做成績分布表:
SQL 語法 |
---|
SELECT 分數級距.級距, 國文級距表.國文, 英文級距表.英文, 數學級距表.數學 FROM (((分數級距 LEFT JOIN (SELECT 級距, Count(級距) AS 國文 FROM 分數評鑑 WHERE 科目=’國文’ GROUP BY 級距) AS 國文級距表 ON 分數級距.級距=國文級距表.級距) LEFT JOIN (SELECT 級距, Count(級距) AS 英文 FROM 分數評鑑 WHERE 科目=’英文’ GROUP BY 級距) AS 英文級距表 ON 分數級距.級距=英文級距表.級距) LEFT JOIN (SELECT 級距, Count(級距) AS 數學 FROM 分數評鑑 WHERE 科目=’數學’ GROUP BY 級距) AS 數學級距表 ON 分數級距.級距=數學級距表.級距) ORDER BY 分數級距.DBID; |
級距 | 國文 | 英文 | 數學 |
---|---|---|---|
滿分 | 1 | ||
優 | 1 | 1 | 1 |
須加強 | 1 | 1 | 2 |
目前想到跟成績有關的大概是這些,如果有再想到新的,再補進本文。
您好:
非常感謝您的文章分享,但好像還缺少交代"國文級距表.國文, 英文級距表.英文, 數學級距表.數學"的建表。可否再加補?謝謝您!
讚讚
這篇寫完很久了,我也忘了當初怎樣做的。
看起來應該是把上面的範例 3 分數級距表轉換成各科的級距表而已。
讚讚
我找到之前做範例的 Access 檔案,看起來應該是當時我測試 Access 2010 用的,所以是 .accdb 檔,我不記得後來我還幹嘛過,因為我開之前,檔案日期是 2011 年,而非 2010 年的,如果有出入的話,我也不確定是怎樣了…
下載檔案位置:https://skydrive.live.com/#cid=145419920BFD55A7&id=145419920BFD55A7%216801
另外這篇後續的文章為:
[SQL] SQL Server 與 Access 不同的怪地方:LEFT JOIN
https://tlcheng.wordpress.com/2010/07/29/sql-sql-server-%e8%88%87-access-%e4%b8%8d%e5%90%8c%e7%9a%84%e6%80%aa%e5%9c%b0%e6%96%b9%ef%bc%9aleft-join/
至於2011被變更過,我懷疑我可能是拿這個檔案測這篇用:
[SQL] Access 在 LEFT JOIN 巢狀語法中使用 INNER JOIN
https://tlcheng.wordpress.com/2011/08/20/sql-access-%e5%9c%a8-left-join-%e5%b7%a2%e7%8b%80%e8%aa%9e%e6%b3%95%e4%b8%ad%e4%bd%bf%e7%94%a8-inner-join/
讚讚
引用通告: [SQL] SQL Server 與 Access 不同的怪地方:LEFT JOIN | 鄭子璉