不同维度的数据统计

最新推荐文章于 2025-05-04 14:28:46 发布

gunsmoke

最新推荐文章于 2025-05-04 14:28:46 发布

阅读量1.9k

点赞数

分类专栏： SQL进阶教程读书笔记

本文链接：https://blog.youkuaiyun.com/gunsmoke/article/details/106414828

版权

SQL进阶教程读书笔记专栏收录该内容

5 篇文章

订阅专栏

需求

在这里插入图片描述

建表语句

CREATE TABLE CourseMaster
(course_id   INTEGER PRIMARY KEY,
 course_name VARCHAR(32) NOT NULL);

INSERT INTO CourseMaster VALUES(1, '会计入门');
INSERT INTO CourseMaster VALUES(2, '财务知识');
INSERT INTO CourseMaster VALUES(3, '簿记考试');
INSERT INTO CourseMaster VALUES(4, '税务师');

CREATE TABLE OpenCourses
(month       INTEGER ,
 course_id   INTEGER ,
    PRIMARY KEY(month, course_id));

INSERT INTO OpenCourses VALUES(200706, 1);
INSERT INTO OpenCourses VALUES(200706, 3);
INSERT INTO OpenCourses VALUES(200706, 4);
INSERT INTO OpenCourses VALUES(200707, 4);
INSERT INTO OpenCourses VALUES(200708, 2);
INSERT INTO OpenCourses VALUES(200708, 4);

解答

SELECT 
	course_name,
	case when course_id in (select course_id from opencourses WHERE month=200706) then 'O' ELSE 'X' end as '六月',
	case when course_id in (select course_id from opencourses WHERE month=200707) then 'O' ELSE 'X' end as '七月',
	case when course_id in (select course_id from opencourses WHERE month=200708) then 'O' ELSE 'X' end as '八月'
FROM
	coursemaster t