SQL資料補全問題
2020-10-25 15:01:11
一、造資料
DROP TABLE IF EXISTS sc;
CREATE TABLE sc (
stu_no VARCHAR(4) COMMENT '學號',
class_no VARCHAR(4) COMMENT '課程號',
grade INT(2) COMMENT '成績'
);
DROP TABLE IF EXISTS class;
CREATE TABLE class (
class_no VARCHAR(4) COMMENT '課程號',
class_name VARCHAR(8) COMMENT '課程名稱'
);
INSERT INTO sc VALUES
('0001','C001',89),
('0001','C002',85),
('0001','C003',99),
('0002','C001',78),
('0002','C002',83),
('0002','C003',86),
('0002','C004',77),
('0003','C001',88),
('0003','C003',68),
('0003','C004',55);
INSERT INTO class VALUES
('C001','語文'),
('C002','數學'),
('C003','英語'),
('C004','科學');
二、檢視資料結構
SELECT * FROM sc;
SELECT * FROM class;
三、需求 （查詢出每個學生每一們課程的分數，如果該學生在資料庫中沒有那門課的分數，則那門課分數置為0）
-
1.使用 cross join 對兩張表做笛卡爾積。
SELECT * FROM sc CROSS JOIN class+--------+----------+-------+----------+------------+ | stu_no | class_no | grade | class_no | class_name | +--------+----------+-------+----------+------------+ | 0001 | C001 | 89 | C001 | 語文 | | 0001 | C001 | 89 | C002 | 數學 | | 0001 | C001 | 89 | C003 | 英語 | | 0001 | C001 | 89 | C004 | 科學 | | 0001 | C002 | 85 | C001 | 語文 | | 0001 | C002 | 85 | C002 | 數學 | | 0001 | C002 | 85 | C003 | 英語 | | 0001 | C002 | 85 | C004 | 科學 | | 0001 | C003 | 99 | C001 | 語文 | | 0001 | C003 | 99 | C002 | 數學 | | 0001 | C003 | 99 | C003 | 英語 | | 0001 | C003 | 99 | C004 | 科學 | | 0002 | C001 | 78 | C001 | 語文 | | 0002 | C001 | 78 | C002 | 數學 | | 0002 | C001 | 78 | C003 | 英語 | | 0002 | C001 | 78 | C004 | 科學 | | 0002 | C002 | 83 | C001 | 語文 | | 0002 | C002 | 83 | C002 | 數學 | | 0002 | C002 | 83 | C003 | 英語 | | 0002 | C002 | 83 | C004 | 科學 | | 0002 | C003 | 86 | C001 | 語文 | | 0002 | C003 | 86 | C002 | 數學 | | 0002 | C003 | 86 | C003 | 英語 | | 0002 | C003 | 86 | C004 | 科學 | | 0002 | C004 | 77 | C001 | 語文 | | 0002 | C004 | 77 | C002 | 數學 | | 0002 | C004 | 77 | C003 | 英語 | | 0002 | C004 | 77 | C004 | 科學 | | 0003 | C001 | 88 | C001 | 語文 | | 0003 | C001 | 88 | C002 | 數學 | | 0003 | C001 | 88 | C003 | 英語 | | 0003 | C001 | 88 | C004 | 科學 | | 0003 | C003 | 68 | C001 | 語文 | | 0003 | C003 | 68 | C002 | 數學 | | 0003 | C003 | 68 | C003 | 英語 | | 0003 | C003 | 68 | C004 | 科學 | | 0003 | C004 | 55 | C001 | 語文 | | 0003 | C004 | 55 | C002 | 數學 | | 0003 | C004 | 55 | C003 | 英語 | | 0003 | C004 | 55 | C004 | 科學 | +--------+----------+-------+----------+------------+ -
2. 將上表中兩個class_no 不相等的資料行，grade 置為0
SELECT stu_no, t1.class_no, t2.class_no, CASE WHEN t1.class_no=t2.class_no THEN grade ELSE 0 END AS grade, class_name FROM sc AS t1 CROSS JOIN class AS t2+--------+----------+----------+-------+------------+ | stu_no | class_no | class_no | grade | class_name | +--------+----------+----------+-------+------------+ | 0001 | C001 | C001 | 89 | 語文 | | 0001 | C001 | C002 | 0 | 數學 | | 0001 | C001 | C003 | 0 | 英語 | | 0001 | C001 | C004 | 0 | 科學 | | 0001 | C002 | C001 | 0 | 語文 | | 0001 | C002 | C002 | 85 | 數學 | | 0001 | C002 | C003 | 0 | 英語 | | 0001 | C002 | C004 | 0 | 科學 | | 0001 | C003 | C001 | 0 | 語文 | | 0001 | C003 | C002 | 0 | 數學 | | 0001 | C003 | C003 | 99 | 英語 | | 0001 | C003 | C004 | 0 | 科學 | | 0002 | C001 | C001 | 78 | 語文 | | 0002 | C001 | C002 | 0 | 數學 | | 0002 | C001 | C003 | 0 | 英語 | | 0002 | C001 | C004 | 0 | 科學 | | 0002 | C002 | C001 | 0 | 語文 | | 0002 | C002 | C002 | 83 | 數學 | | 0002 | C002 | C003 | 0 | 英語 | | 0002 | C002 | C004 | 0 | 科學 | | 0002 | C003 | C001 | 0 | 語文 | | 0002 | C003 | C002 | 0 | 數學 | | 0002 | C003 | C003 | 86 | 英語 | | 0002 | C003 | C004 | 0 | 科學 | | 0002 | C004 | C001 | 0 | 語文 | | 0002 | C004 | C002 | 0 | 數學 | | 0002 | C004 | C003 | 0 | 英語 | | 0002 | C004 | C004 | 77 | 科學 | | 0003 | C001 | C001 | 88 | 語文 | | 0003 | C001 | C002 | 0 | 數學 | | 0003 | C001 | C003 | 0 | 英語 | | 0003 | C001 | C004 | 0 | 科學 | | 0003 | C003 | C001 | 0 | 語文 | | 0003 | C003 | C002 | 0 | 數學 | | 0003 | C003 | C003 | 68 | 英語 | | 0003 | C003 | C004 | 0 | 科學 | | 0003 | C004 | C001 | 0 | 語文 | | 0003 | C004 | C002 | 0 | 數學 | | 0003 | C004 | C003 | 0 | 英語 | | 0003 | C004 | C004 | 55 | 科學 | +--------+----------+----------+-------+------------+ -
3.觀察上一個步驟中的資料不難發現，我們已經將每個學生缺失的課程分數用0補全，但是原本就正常的資料又重複了一些課程為0的資料，所以需要去重，去重無非就是用group by 或者 distinct ,但是distinct 不可控制，所以我們這裡選用 group by 來去重得出我們想要的資料。
SELECT stu_no, t2.class_no, SUM(CASE WHEN t1.class_no=t2.class_no THEN grade ELSE 0 END ) AS grade, class_name FROM sc AS t1 CROSS JOIN class AS t2 GROUP BY stu_no,t2.class_no,class_name;
