SELECT
CASE
WHEN month_num IN (12, 1, 2) THEN 'Winter'
WHEN month_num IN (3, 4, 5) THEN 'Spring'
WHEN month_num in (6, 7, 8) THEN 'Summer'
ELSE 'Fall'
END AS season,
AVG(temperature) AS average_temperature, -- 用 AVG() 處理資料
year_num
FROM weather
GROUP BY season, year_num; -- 記得對未彙總欄位進行分組
搭配 CASE 的彙總
彙總經 CASE 處理的欄位
SELECT
season,
AVG(
CASE
WHEN season = 'Winter' THEN temperature - 30
WHEN season IN ('Spring', 'Fall') THEN temperature - 60
WHEN season = 'Summer' THEN temperature - 75
END
) AS relation_to_average_temperature
FROM weather
GROUP BY season;
彙總經 CASE 處理的欄位
JOIN
SELECT
...
-- 定義 CASE 陳述式
CASE ...
FROM <left_table>
-- 將新紀錄與既有資料表 JOIN
LEFT JOIN <right_table>
...
;
可以使用來自兩個不同資料表的資料來建構 CASE 陳述式。
在 CASE 中使用多個資料表的欄位
LEFT、RIGHT、INNER、OUTER 等
可與其他工具結合，包括彙總函式
$$
$$
一起來看看吧！
判定大學學分資格
SELECT
students.student_name,
student_courses.course_name,
-- 同時評估學生的年級數與該課成績
CASE
WHEN students.grade_num = 12 AND student_courses.grade > 90
THEN 'College Credit Eligible'
ELSE 'Too Early for College Credit'
END AS college_credit_status
FROM student_courses
LEFT JOIN students ON student_courses.student_id = students.student_id;
Preparing Video For Download...
