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, -- Manipulate data with AVG()
year_num
FROM weather
GROUP BY season, year_num; -- Remember to group by non-aggregated fields
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
...
-- Define a CASE statement
CASE ...
FROM <left_table>
-- JOIN new records to existing table
LEFT JOIN <right_table>
...
;
CASEは、2つのテーブルを結合したデータでも構築できます。
CASEで複数テーブルの列を使用
LEFT、RIGHT、INNER、OUTER など
集計関数など他の機能と組み合わせ可能
$$
$$
見ていきましょう。
単位認定可否の判定
SELECT
students.student_name,
student_courses.course_name,
-- Evaluate both the student's grade number and grade for the course
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...
