表之间的数据匹配（生成交叉表）--sql

表之间的数据匹配（生成交叉表）--sql

创建表、添加数据

CREATE TABLE CourseMaster
(
	course_id int not null primary key,
	coures_name varchar(20)
);


CREATE TABLE OpenCourses
(
month int,
course_id int not null ,
CONSTRAINT fk_course_id FOREIGN KEY (course_id)
REFERENCES CourseMaster(course_id)
);


insert into CourseMaster(course_id,coures_name) 
values
(1,'会计入门 '),
(2,'财务知识 '),
(3,'簿记考试 '),
(4,'税务师 ');


insert into OpenCourses(month,course_id) 
values
(200706,1),
(200706,3),
(200706,1),
(200706,4),
(200707,4),
(200708,2),
(200708,4);

生成交叉表

我们要用这两张表来生成交叉表，以便于一目了然地知道每个月开设的课程。

使用 IN 谓词

-- 表的匹配 ：使用 IN 谓词
SELECT coures_name AS 课程名,
	CASE WHEN course_id IN 
						(SELECT course_id FROM opencourses WHERE month = 200706) THEN 'o'
			 ELSE 'x' END AS "6月",
    CASE WHEN course_id IN
						(SELECT course_id FROM opencourses WHERE month = 200707) THEN 'o'
			 ELSE 'x' END AS "7月",
	CASE WHEN course_id IN
						(SELECT course_id FROM opencourses WHERE month = 200708) THEN 'o'
			 ELSE 'x' END AS "8月"
FROM coursemaster;

使用 EXISTS 谓词

-- 表的匹配 ：使用 EXISTS 谓词
SELECT CM.coures_name AS 课程名,
	CASE WHEN EXISTS
				(SELECT course_id FROM opencourses oc WHERE month = 200706 AND oc.course_id=cm.course_id) THEN 'o' 
				ELSE 'x' END as "6月",
	CASE WHEN EXISTS
				(SELECT course_id FROM opencourses oc WHERE month = 200707 AND oc.course_id=cm.course_id) THEN 'o' 
			 	ELSE 'x' END as "7月",
	CASE WHEN EXISTS
				(SELECT course_id FROM opencourses oc WHERE month = 200708 AND oc.course_id=cm.course_id) THEN 'o' 
			 	ELSE 'x' END as "8月"
FROM coursemaster CM;

图例