写在前面
SQL是面向集合的语言，与面向过程和面向对象语言都不一样
寻找缺失的编号
/* 寻找缺失的编号 */
CREATE TABLE SeqTbl
(seq INTEGER PRIMARY KEY,
name VARCHAR(16) NOT NULL);
INSERT INTO SeqTbl VALUES(1, '迪克');
INSERT INTO SeqTbl VALUES(2, '安');
INSERT INTO SeqTbl VALUES(3, '莱露');
INSERT INTO SeqTbl VALUES(5, '卡');
INSERT INTO SeqTbl VALUES(6, '玛丽');
INSERT INTO SeqTbl VALUES(8, '本');
-- 如果有查询结果，说明存在缺失的编号
SELECT '存在缺失的编号' FROM SeqTbl HAVING COUNT(*) <> MAX(seq);
新的SQL标准里HAVING可以单独使用
-- 查询缺失编号的最小值，如果表包含NULL，NOT IN可能得不到正确结果
SELECT MIN(seq+1) AS gap FROM SeqTbl WHERE (seq+1) NOT IN (SELECT seq FROM SeqTbl);
用HAVING子句进行子查询：求众数
/* 用HAVING子句进行子查询：求众数(求中位数时也用本代码) */
CREATE TABLE Graduates
(name VARCHAR(16) PRIMARY KEY,
income INTEGER NOT NULL);
INSERT INTO Graduates VALUES('桑普森', 400000);
INSERT INTO Graduates VALUES('迈克', 30000);
INSERT INTO Graduates VALUES('怀特', 20000);
INSERT INTO Graduates VALUES('阿诺德', 20000);
INSERT INTO Graduates VALUES('史密斯', 20000);
INSERT INTO Graduates VALUES('劳伦斯', 15000);
INSERT INTO Graduates VALUES('哈德逊', 15000);
INSERT INTO Graduates VALUES('肯特', 10000);
INSERT INTO Graduates VALUES('贝克', 10000);
INSERT INTO Graduates VALUES('斯科特', 10000);
-- 求众数的SQL语句(1):使用谓词
SELECT income,COUNT(*) FROM Graduates GROUP BY income HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM Graduates GROUP BY income);
-- 求众数的SQL语句(2):使用极值函数
SELECT income,count(*) FROM Graduates GROUP BY income HAVING COUNT(*) >= (SELECT MAX(cnt) FROM (SELECT COUNT(*) as cnt FROM Graduates GROUP BY income) AS tmp);
用HAVING子句进行自连接：求中位数
-- 求中位数的SQL语句：在HAVING子句中使用非等值自连接
SELECT AVG(income) FROM
(SELECT T1.income FROM Graduates T1,Graduates T2 GROUP BY T1.income HAVING SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END) >= COUNT(*)/2 AND SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END) >= COUNT(*)/2) AS TMP;
查询不包含NULL的集合
COUNT函数的使用方法有COUNT(*)和COUNT(<字段名>)两种，区别在于：
- COUNT(*)可以用于NULL，而COUNT(<列名>)与其他聚合函数一样，要先排除掉null再进行统计
- COUNT(*)查的是所有行的数目，而COUNT(<列名>)不一定是
/* 查询不包含NULL的集合 */
CREATE TABLE Students
(student_id INTEGER PRIMARY KEY,
dpt VARCHAR(16) NOT NULL,
sbmt_date DATE);
INSERT INTO Students VALUES(100, '理学院', '2005-10-10');
INSERT INTO Students VALUES(101, '理学院', '2005-09-22');
INSERT INTO Students VALUES(102, '文学院', NULL);
INSERT INTO Students VALUES(103, '文学院', '2005-09-10');
INSERT INTO Students VALUES(200, '文学院', '2005-09-22');
INSERT INTO Students VALUES(201, '工学院', NULL);
INSERT INTO Students VALUES(202, '经济学院', '2005-09-25');
-- 查询"sbmt_date"列不包含NULL的列(1):使用COUNT
SELECT dpt FROM Students GROUP BY dpt HAVING COUNT(*) = COUNT(sbmt_date);
-- 查询"sbmt_date"列不包含NULL的列(2):使用CASE表达式
SELECT dpt FROM Students GROUP BY dpt HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL THEN 1 ELSE 0 END);
用关系除法进行购物篮分析
/* 用关系除法运算进行购物篮分析 */
CREATE TABLE Items
(item VARCHAR(16) PRIMARY KEY);
CREATE TABLE ShopItems
(shop VARCHAR(16),
item VARCHAR(16),
PRIMARY KEY(shop, item));
INSERT INTO Items VALUES('啤酒');
INSERT INTO Items VALUES('纸尿裤');
INSERT INTO Items VALUES('自行车');
INSERT INTO ShopItems VALUES('仙台', '啤酒');
INSERT INTO ShopItems VALUES('仙台', '纸尿裤');
INSERT INTO ShopItems VALUES('仙台', '自行车');
INSERT INTO ShopItems VALUES('仙台', '窗帘');
INSERT INTO ShopItems VALUES('东京', '啤酒');
INSERT INTO ShopItems VALUES('东京', '纸尿裤');
INSERT INTO ShopItems VALUES('东京', '自行车');
INSERT INTO ShopItems VALUES('大阪', '电视');
INSERT INTO ShopItems VALUES('大阪', '纸尿裤');
INSERT INTO ShopItems VALUES('大阪', '自行车');
-- 查到items表里商品都有的shop名称
SELECT SI.shop FROM ShopItems AS SI,Items AS I WHERE SI.item = I.item GROUP BY SI.shop
HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items);
-- 查找全都有且只有items表中商品的shop名称
SELECT SI.shop FROM ShopItems AS SI LEFT JOIN Items AS I ON SI.item = I.item GROUP BY SI.shop HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items) AND
COUNT(I.item) = (SELECT COUNT(item) FROM Items);
小结
- 表不是文件，记录也没有顺序，所以SQL不进行排序
- SQL不是面向过程语言，没有循环、条件分支和赋值操作
- SQL通过不断生成子集来求得目标集合
- GROUP BY子句可以用来生成子集
- WHERE子句用来调查集合元素的性质，而HAVING子句用来调查集合本身的性质
练习题
-- 1-4-1 修改编号缺失的逻辑，使结果总是返回一行数据
SELECT CASE WHEN COUNT(*) <> MAX(seq) THEN '存在缺失的编号' ELSE '不存在缺失的编号' END AS col FROM SeqTbl;
-- 1-4-2 练习"特征函数"
SELECT dpt FROM Students GROUP BY dpt HAVING COUNT(*) = SUM(CASE WHEN sbmt_date BETWEEN '2005-09-01' AND '2005-09-30' THEN 1 ELSE 0 END);
-- 1-4-3 购物篮分析问题的一般化
SELECT shop,COUNT(I.item) AS my_item_cnt,(SELECT COUNT(*) FROM Items) - COUNT(I.item) AS diff_cnt FROM ShopItems AS SI LEFT JOIN Items AS I ON SI.item = I.item GROUP BY shop;