sql分段統計

㈠ 圖解SQL面試題：經典50題

已知有四張表，分別為學生表（student）、成績表（score）、課程表（course）和教師表（teacher）。根據這些信息，需要編寫SQL語句來解決以下問題。

首先，創建資料庫和表。在客戶端navicat中，創建學生表（student），設置「學號」為主鍵約束，並定義其他列的數據類型和約束。接著，創建成績表（score），設置「課程號」和「學號」為主鍵約束，定義「成績」列的數據類型。創建課程表（course），設置「課程號」為主鍵約束。創建教師表（teacher），設置「教師號」為主鍵約束。

然後，向表中添加數據。使用SQL語句和navicat操作向學生表（student）、成績表（score）、課程表（course）和教師表（teacher）中添加數據。

接下來，進行50道面試題的解答。分類包括簡單查詢、匯總分析、復雜查詢和多表查詢。

簡單查詢包括：查詢姓「猴」的學生名單，查詢姓「孟」老師的個數。

匯總分析問題包括：查詢課程編號為「0002」的總成績，查詢選了課程的學生人數，查詢各科成績最高和最低的分，查詢每門課程被選修的學生數，查詢男生、女生人數，查詢平均成績大於60分學生的學號和平均成績，查詢至少選修兩門課程的學生學號，查詢同名同姓學生名單並統計同名人數，查詢不及格的課程並按課程號從大到小排列，查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列，檢索課程編號為「0004」且分數小於60的學生學號，結果按分數降序排列，統計每門課程的學生選修人數（超過2人的課程才統計），要求輸出課程號和選修人數，查詢兩門以上不及格課程的同學的學號及其平均成績，查詢學生的總成績並進行排名，查詢平均成績大於60分的學生的學號和平均成績。

復雜查詢包括：查詢所有課程成績小於60分學生的學號、姓名，查詢沒有學全所有課的學生的學號、姓名，查詢出只選修了兩門課程的全部學生的學號和姓名，查詢1990年出生的學生名單，查詢各科成績前兩名的記錄。

多表查詢問題包括：查詢所有學生的學號、姓名、選課數、總成績，查詢平均成績大於85的所有學生的學號、姓名和平均成績，查詢學生的選課情況：學號，姓名，課程號，課程名稱，查詢出每門課程的及格人數和不及格人數，使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計：各分數段人數，課程號和課程名稱，查詢課程編號為0003且課程成績在80分以上的學生的學號和姓名。

高級功能：窗口函數，包括查詢學生平均成績及其名次，按各科成績進行排序並顯示排名，查詢每門功成績最好的前兩名學生姓名，查詢所有課程的成績第2名到第3名的學生信息及該課程成績，查詢各科成績前三名的記錄。

解答這50道面試題需要理解SQL的基礎語法，熟悉表之間的關聯關系，並掌握查詢語句的編寫技巧。通過實際操作和理解題目的邏輯，可以有效地提升SQL編程能力。

㈡ SQL語句的多表查詢

在處理SQL多表查詢時，我們需要關注幾個關鍵點：合並兩個表、內聯結、左聯結、右聯結、查詢業務邏輯、平均成績篩選、選課情況統計、成績段統計、分組查詢和特定數據的篩選。

開始練習合並兩個表時，使用 UNION ALL 語句可以將兩個表的數據合並，並保持重復項。這種操作對數據整合特別有用。

內聯結涉及到在 from 子句中同時使用兩張表，並通過別名（如 student as a）來區分它們。在列名中，我們使用 "a.列名" 來引用特定表的數據。使用 INNER JOIN 來篩選同時存在於兩張表中的數據，通過 ON 子句定義關聯條件（例如，通過學號）。內聯結就像是建立了一座橋梁，連接了兩個表。

左聯結（LEFT JOIN）允許我們保留表A中的所有記錄，即使在表B中沒有匹配的記錄。我們通過添加 WHERE 子句來過濾結果，確保只包括表A中的數據。這里的 is null 判斷用於找出表B中學號為空值的記錄。

右聯結（RIGHT JOIN）則反之，保留表B中的所有記錄，即使在表A中沒有匹配項。同樣，WHERE 子句可以用於過濾結果，確保只包括表B中的數據。

為了獲取所有學生的學號、姓名、選課數和總成績，我們需要翻譯業務需求為SQL語句，使用 SELECT 子句列出所需欄位，使用 JOIN 連接表，並可能使用聚合函數如 COUNT 或 SUM 來計算選課數和總成績。

查詢平均成績大於85的所有學生信息，可以使用 SELECT 和 WHERE 子句來篩選滿足條件的記錄，並使用 AVG 函數計算平均成績。

查詢學生的選課情況，包括學號、姓名、課程號和課程名，使用 SELECT 子句列出相關欄位，並使用 JOIN 連接表。

統計每門課程的及格人數和不及格人數，使用 CASE 表達式來判斷成績是否及格，並在 SELECT 子句中使用 COUNT 函數來計數。確保 CASE 表達式遵循正確的語法結構，包括 else 和 end。

使用分段統計各科成績的人數，可以使用 CASE 表達式對成績進行分類，並使用 GROUP BY 子句來對結果進行分組。注意，即使在 GROUP BY 子句中使用了特定欄位（如課程名稱），分組結果仍然基於成績段。

最終，通過掌握這些基本技巧和理解 SQL 的聯結操作，可以有效地處理多表查詢任務，並解決各種業務邏輯問題。

㈢ SQL：對score表進行分段統計

select 課程編號,
sum(case when 成績=N'優' then 1 else 0 end) 優,
sum(case when 成績=N'良' then 1 else 0 end) 良,
sum(case when 成績=N'中' then 1 else 0 end) 中,
sum(case when 成績=N'差' then 1 else 0 end) 差,
...........

count(*) as 人數合計
from score
group by 課程編號