現(xiàn)有表1CourseMaster(包含course_id,course_name),表2OpenCourses(包含month,course_id ),現(xiàn)在要求用這兩張表通過(guò)SQL代碼,生成如下圖所示的交叉表:

解法:

/* 表的匹配：使用IN謂詞 */

SELECT CM.course_name,

CASE WHEN CM.course_id IN

(SELECT course_id FROM OpenCourses

WHERE month = 200706) THEN '○'

ELSE '×' END AS "6月",

CASE WHEN CM.course_id IN

(SELECT course_id FROM OpenCourses

WHERE month = 200707) THEN '○'

ELSE '×' END AS "7月",

CASE WHEN CM.course_id IN

(SELECT course_id FROM OpenCourses

WHERE month = 200708) THEN '○'

ELSE '×' END AS "8月"

FROM CourseMaster CM;