现有表1CourseMaster(包含course_id,course_name),表2OpenCourses(包含month,course_id ),现在要求用这两张表通过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;