代码拉取完成，页面将自动刷新
# 练习题1-1-：多列数据的最大值
CREATE TABLE Greatests
(key CHAR(1) PRIMARY KEY,
x INTEGER NOT NULL,
y INTEGER NOT NULL,
z INTEGER NOT NULL);
INSERT INTO Greatests VALUES('A', 1, 2, 3);
INSERT INTO Greatests VALUES('B', 5, 5, 2);
INSERT INTO Greatests VALUES('C', 4, 7, 1);
INSERT INTO Greatests VALUES('D', 3, 3, 8);
-- 求x和y二者中较大的值
SELECT key,
CASE WHEN x < y THEN y
ELSE x END AS greatest
FROM Greatests;
-- 求x和y和z三者中的最大值
SELECT key,
CASE WHEN CASE WHEN x < y THEN y ELSE x END < z
THEN z
ELSE CASE WHEN x < y THEN y ELSE x END
END AS greatest
FROM Greatests;
-- Oracle、MySQL、PostgreSQL
SELECT key, GREATEST(GREATEST(x,y), z) AS greatest
FROM Greatests;
# 练习题1-1-2：转换行列——在表头里加入汇总和再揭
CREATE TABLE PopTbl2
(pref_name VARCHAR(32),
sex CHAR(1) NOT NULL,
population INTEGER NOT NULL,
PRIMARY KEY(pref_name, sex));
INSERT INTO PopTbl2 VALUES('德岛', '1', 60 );
INSERT INTO PopTbl2 VALUES('德岛', '2', 40 );
INSERT INTO PopTbl2 VALUES('香川', '1', 100);
INSERT INTO PopTbl2 VALUES('香川', '2', 100);
INSERT INTO PopTbl2 VALUES('爱媛', '1', 100);
INSERT INTO PopTbl2 VALUES('爱媛', '2', 50 );
INSERT INTO PopTbl2 VALUES('高知', '1', 100);
INSERT INTO PopTbl2 VALUES('高知', '2', 100);
INSERT INTO PopTbl2 VALUES('福冈', '1', 100);
INSERT INTO PopTbl2 VALUES('福冈', '2', 200);
INSERT INTO PopTbl2 VALUES('佐贺', '1', 20 );
INSERT INTO PopTbl2 VALUES('佐贺', '2', 80 );
INSERT INTO PopTbl2 VALUES('长崎', '1', 125);
INSERT INTO PopTbl2 VALUES('长崎', '2', 125);
INSERT INTO PopTbl2 VALUES('东京', '1', 250);
INSERT INTO PopTbl2 VALUES('东京', '2', 150);
SELECT sex AS 性别,
SUM(population) AS 全国,
SUM(CASE WHEN pref_name = '德岛'
THEN population ELSE 0 END) AS 德岛,
SUM(CASE WHEN pref_name = '香川'
THEN population ELSE 0 END) AS 香川,
SUM(CASE WHEN pref_name = '爱媛'
THEN population ELSE 0 END) AS 爱媛,
SUM(CASE WHEN pref_name = '高知'
THEN population ELSE 0 END) AS 高知,
SUM(CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知')
THEN population ELSE 0 END) AS 四国
FROM PopTbl2
GROUP BY sex;
练习题1-1-3：用ORDER BY生成“排序”列
SELECT key
FROM Greatests
ORDER BY CASE key
WHEN 'B' THEN 1
WHEN 'A' THEN 2
WHEN 'D' THEN 3
WHEN 'C' THEN 4
ELSE NULL END;
SELECT key,
CASE key
WHEN 'B' THEN 1
WHEN 'A' THEN 2
WHEN 'D' THEN 3
WHEN 'C' THEN 4
ELSE NULL END AS sort_col
FROM Greatests
ORDER BY sort_col;
练习题1-2-1：窗口函数的结果预测(1)
CREATE TABLE ServerLoadSample
(server CHAR(4) NOT NULL,
sample_date DATE NOT NULL,
load_val INTEGER NOT NULL,
PRIMARY KEY (server, sample_date));
INSERT INTO ServerLoadSample VALUES('A' , '2018-02-01', 1024);
INSERT INTO ServerLoadSample VALUES('A' , '2018-02-02', 2366);
INSERT INTO ServerLoadSample VALUES('A' , '2018-02-05', 2366);
INSERT INTO ServerLoadSample VALUES('A' , '2018-02-07', 985);
INSERT INTO ServerLoadSample VALUES('A' , '2018-02-08', 780);
INSERT INTO ServerLoadSample VALUES('A' , '2018-02-12', 1000);
INSERT INTO ServerLoadSample VALUES('B' , '2018-02-01', 54);
INSERT INTO ServerLoadSample VALUES('B' , '2018-02-02', 39008);
INSERT INTO ServerLoadSample VALUES('B' , '2018-02-03', 2900);
INSERT INTO ServerLoadSample VALUES('B' , '2018-02-04', 556);
INSERT INTO ServerLoadSample VALUES('B' , '2018-02-05', 12600);
INSERT INTO ServerLoadSample VALUES('B' , '2018-02-06', 7309);
INSERT INTO ServerLoadSample VALUES('C' , '2018-02-01', 1000);
INSERT INTO ServerLoadSample VALUES('C' , '2018-02-07', 2000);
INSERT INTO ServerLoadSample VALUES('C' , '2018-02-16', 500);
SELECT server, sample_date,
SUM(load_val) OVER () AS sum_load
FROM ServerLoadSample;
练习题1-2-2：窗口函数的结果预测(2)
SELECT server, sample_date,
SUM(load_val) OVER (PARTITION BY server) AS sum_load
FROM ServerLoadSample;
练习题1-3-1：可重组合
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1 INNER JOIN Products P2
ON P1.name >= P2.name;
练习题1-3-2：使用窗口函数去重
DROP TABLE Products;
CREATE TABLE Products
(name VARCHAR(16) NOT NULL,
price INTEGER NOT NULL);
-- 重复的记录
INSERT INTO Products VALUES('苹果', 50);
INSERT INTO Products VALUES('橘子', 100);
INSERT INTO Products VALUES('橘子', 100);
INSERT INTO Products VALUES('橘子', 100);
INSERT INTO Products VALUES('香蕉', 80);
-- 创建(name, price) 分组的连续编号唯一的表
CREATE TABLE Products_NoRedundant
AS
SELECT ROW_NUMBER()
OVER(PARTITION BY name, price
ORDER BY name) AS row_num,
name, price
FROM Products;
-- 删除编号1 之外的记录
DELETE FROM Products_NoRedundant
WHERE row_num > 1;
DELETE FROM
(SELECT ROW_NUMBER()
OVER(PARTITION BY name, price
ORDER BY name) AS row_num
FROM Products)
WHERE row_num > 1;
练习题1-5-1：数组表——行结构表的情况
/* 1-5-1：数组表——行结构表的情况 */
CREATE TABLE ArrayTbl2
(key CHAR(1) NOT NULL,
i INTEGER NOT NULL,
val INTEGER,
PRIMARY KEY (key, i));
/* A全为NULL、B仅有一个为非NULL、C全为非NULL */
INSERT INTO ArrayTbl2 VALUES('A', 1, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 2, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 3, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 4, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 5, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 6, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 7, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 8, NULL);
INSERT INTO ArrayTbl2 VALUES('A', 9, NULL);
INSERT INTO ArrayTbl2 VALUES('A',10, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 1, 3);
INSERT INTO ArrayTbl2 VALUES('B', 2, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 3, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 4, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 5, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 6, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 7, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 8, NULL);
INSERT INTO ArrayTbl2 VALUES('B', 9, NULL);
INSERT INTO ArrayTbl2 VALUES('B',10, NULL);
INSERT INTO ArrayTbl2 VALUES('C', 1, 1);
INSERT INTO ArrayTbl2 VALUES('C', 2, 1);
INSERT INTO ArrayTbl2 VALUES('C', 3, 1);
INSERT INTO ArrayTbl2 VALUES('C', 4, 1);
INSERT INTO ArrayTbl2 VALUES('C', 5, 1);
INSERT INTO ArrayTbl2 VALUES('C', 6, 1);
INSERT INTO ArrayTbl2 VALUES('C', 7, 1);
INSERT INTO ArrayTbl2 VALUES('C', 8, 1);
INSERT INTO ArrayTbl2 VALUES('C', 9, 1);
INSERT INTO ArrayTbl2 VALUES('C',10, 1);
-- 错误解法
SELECT DISTINCT key
FROM ArrayTbl2 AT1
WHERE NOT EXISTS
(SELECT *
FROM ArrayTbl2 AT2
WHERE AT1.key = AT2.key
AND AT2.val <> 1);
-- 正确解法
SELECT DISTINCT key
FROM ArrayTbl2 A1
WHERE NOT EXISTS
(SELECT *
FROM ArrayTbl2 A2
WHERE A1.key = A2.key
AND (A2.val <> 1 OR A2.val IS NULL));
-- 其他解法1 ：使用ALL谓词
SELECT DISTINCT key
FROM ArrayTbl2 A1
WHERE 1 = ALL
(SELECT val
FROM ArrayTbl2 A2
WHERE A1.key = A2.key);
-- 其他解法2 ：使用HAVING子句
SELECT key
FROM ArrayTbl2
GROUP BY key
HAVING SUM(CASE WHEN val = 1 THEN 1 ELSE 0 END) = 10;
-- 其他解法3 ：在HAVING子句中使用极值函数
SELECT key
FROM ArrayTbl2
GROUP BY key
HAVING MAX(val) = 1
AND MIN(val) = 1;
练习题1-5-2：使用ALL谓词表达全称量化
-- 查找已经完成到工程1的项目：使用ALL谓词解答
SELECT *
FROM Projects P1
WHERE '○' = ALL
(SELECT CASE WHEN step_nbr <= 1
AND status = '完成'
THEN '○'
WHEN step_nbr > 1
AND status = '等待'
THEN '○'
ELSE '×' END
FROM Projects P2
WHERE P1.project_id = P2. project_id);
练习题1-5-3：求质数
CREATE TABLE Digits
(digit INTEGER PRIMARY KEY);
INSERT INTO Digits VALUES (0);
INSERT INTO Digits VALUES (1);
INSERT INTO Digits VALUES (2);
INSERT INTO Digits VALUES (3);
INSERT INTO Digits VALUES (4);
INSERT INTO Digits VALUES (5);
INSERT INTO Digits VALUES (6);
INSERT INTO Digits VALUES (7);
INSERT INTO Digits VALUES (8);
INSERT INTO Digits VALUES (9);
DROP TABLE Numbers;
CREATE TABLE Numbers
AS
SELECT D1.digit + (D2.digit * 10) AS num
FROM Digits D1 CROSS JOIN Digits D2
WHERE D1.digit + (D2.digit * 10) BETWEEN 1 AND 100;
-- 解法：用NOT EXISTS表达全称量化
SELECT num AS prime
FROM Numbers Dividend
WHERE num > 1
AND NOT EXISTS
(SELECT *
FROM Numbers Divisor
WHERE Divisor.num <= Dividend.num / 2
AND Divisor.num <> 1 --1 は約数に含まない
AND MOD(Dividend.num, Divisor.num) = 0)
ORDER BY prime;
练习题1-6-1：寻找缺失的编号——升级版
SELECT '存在缺失的编号' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq)
UNION ALL
SELECT '不存在缺失的编号' AS gap
FROM SeqTbl
HAVING COUNT(*) = MAX(seq);
SELECT CASE WHEN COUNT(*) <> MAX(seq)
THEN '存在缺失的编号'
ELSE '不存在缺失的编号' END AS gap
FROM SeqTbl;
练习题1-6-2：练习特征函数
-- 查找所有学生都在9月份提交完成的学院(1)：使用BETWEEN谓词
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date BETWEEN '2018-09-01'
AND '2018-09-30'
THEN 1 ELSE 0 END);
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN EXTRACT (YEAR FROM sbmt_date) = 2018
AND EXTRACT (MONTH FROM sbmt_date) = 09
THEN 1 ELSE 0 END);
练习题1-6-3：关系除法运算的优化
SELECT SI.shop,
COUNT(SI.item) AS my_item_cnt,
(SELECT COUNT(item) FROM Items) - COUNT(SI.item) AS diff_cnt
FROM ShopItems SI INNER JOIN Items I
ON SI.item = I.item
GROUP BY SI.shop;
练习题1-7-1：移动平均值(1)
-- 使用关联子查询求移动平均值
SELECT prc_date, A1.prc_amt,
(SELECT AVG(prc_amt)
FROM Accounts A2
WHERE A1.prc_date >= A2.prc_date
AND (SELECT COUNT(*)
FROM Accounts A3
WHERE A3.prc_date
BETWEEN A2.prc_date
AND A1.prc_date ) <= 3 ) AS mvg_sum
FROM Accounts A1
ORDER BY prc_date;
-- 去掉聚合并输出
SELECT A1.prc_date AS A1_date,
A2.prc_date AS A2_date,
A2.prc_amt AS amt
FROM Accounts A1, Accounts A2
WHERE A1.prc_date >= A2.prc_date
AND (SELECT COUNT(*)
FROM Accounts A3
WHERE A3.prc_date BETWEEN A2.prc_date
AND A1.prc_date ) <= 3
ORDER BY A1_date, A2_date;
练习题1-7-2：移动平均值(2)
-- 窗口函数
SELECT prc_date, prc_amt,
CASE WHEN cnt < 3 THEN NULL
ELSE mvg_avg END AS mvg_avg
FROM (SELECT prc_date, prc_amt,
AVG(prc_amt)
OVER(ORDER BY prc_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) mvg_avg,
COUNT(*)
OVER (ORDER BY prc_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS cnt
FROM Accounts) TMP;
-- 关联子查询
SELECT prc_date, A1.prc_amt,
(SELECT AVG(prc_amt)
FROM Accounts A2
WHERE A1.prc_date >= A2.prc_date
AND (SELECT COUNT(*)
FROM Accounts A3
WHERE A3.prc_date
BETWEEN A2.prc_date AND A1.prc_date ) <= 3
HAVING COUNT(*) =3) AS mvg_sum -- 不满3行数据就不显示
FROM Accounts A1
ORDER BY prc_date;
练习题1-8-1：先连接还是先聚合
-- 去掉一个内联视图后的修正版
SELECT MASTER.age_class AS age_class,
MASTER.sex_cd AS sex_cd,
SUM(CASE WHEN pref_name IN ('青森', '秋田')
THEN population ELSE NULL END) AS pop_tohoku,
SUM(CASE WHEN pref_name IN ('东京', '千叶')
THEN population ELSE NULL END) AS pop_kanto
FROM (SELECT age_class, sex_cd
FROM TblAge CROSS JOIN TblSex) MASTER
LEFT OUTER JOIN TblPop DATA
ON MASTER.age_class = DATA.age_class
AND MASTER.sex_cd = DATA.sex_cd
GROUP BY MASTER.age_class, MASTER.sex_cd;
练习题1-8-2：请留意孩子的人数
SELECT EMP.employee, COUNT(*) AS child_cnt
FROM Personnel EMP
LEFT OUTER JOIN Children
ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3)
GROUP BY EMP.employee;
SELECT EMP.employee, COUNT(CHILDREN.child) AS child_cnt
FROM Personnel EMP
LEFT OUTER JOIN Children
ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3)
GROUP BY EMP.employee;
练习题1-8-3：全外连接和MERGE运算符
MERGE INTO Class_A A
USING (SELECT *
FROM Class_B ) B
ON (A.id = B.id)
WHEN MATCHED THEN
UPDATE SET A.name = B.name
WHEN NOT MATCHED THEN
INSERT (id, name)
VALUES (B.id, B.name);
练习题1-9-1：改进“只使用UNION的比较”
SELECT CASE WHEN COUNT(*) = (SELECT COUNT(*) FROM tbl_A )
AND COUNT(*) = (SELECT COUNT(*) FROM tbl_B )
THEN '相等'
ELSE '不相等' END AS result
FROM ( SELECT *
FROM tbl_A
UNION
SELECT *
FROM tbl_B ) TMP;
练习题1-9-2：精确关系除法运算
SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE NOT EXISTS
(SELECT skill
FROM Skills
EXCEPT
SELECT skill
FROM EmpSkills ES2
WHERE ES1.emp = ES2.emp)
AND NOT EXISTS
(SELECT skill
FROM EmpSkills ES3
WHERE ES1.emp = ES3.emp
EXCEPT
SELECT skill
FROM Skills );
SELECT emp
FROM EmpSkills ES1
WHERE NOT EXISTS
(SELECT skill
FROM Skills
EXCEPT
SELECT skill
FROM EmpSkills ES2
WHERE ES1.emp = ES2.emp)
GROUP BY emp
HAVING COUNT(*) = (SELECT COUNT(*) FROM Skills);
练习题1-10-1：求所有的缺失编号——NOT EXISTS和外连接
-- NOT EXISTS版本
SELECT seq
FROM Sequence N
WHERE seq BETWEEN 1 AND 12
AND NOT EXISTS
(SELECT *
FROM SeqTbl S
WHERE N.seq = S.seq );
SELECT N.seq
FROM Sequence N LEFT OUTER JOIN SeqTbl S
ON N.seq = S.seq
WHERE N.seq BETWEEN 1 AND 12
AND S.seq IS NULL;
练习题1-10-2：求序列——面向集合的思想
SELECT S1.seat AS start_seat, '~' , S2.seat AS end_seat
FROM Seats S1, Seats S2, Seats S3
WHERE S2.seat = S1.seat + (:head_cnt - 1)
AND S3.seat BETWEEN S1.seat AND S2.seat
GROUP BY S1.seat, S2.seat
HAVING COUNT(*) = SUM(CASE WHEN S3.status = '未预定' THEN 1 ELSE 0 END);
-- 座位有换排时
SELECT S1.seat AS start_seat, '~' , S2.seat AS end_seat
FROM Seats2 S1, Seats2 S2, Seats2 S3
WHERE S2.seat = S1.seat + (:head_cnt -1)
AND S3.seat BETWEEN S1.seat AND S2.seat
GROUP BY S1.seat, S2.seat
HAVING COUNT(*) = SUM(CASE WHEN S3.status = '未预定'
AND S3.line_id = S1.line_id
THEN 1 ELSE 0 END);
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。
马建仓 AI 助手