这道题目我觉得很经典,所以记录下来。
表的结果如下:
一个实体对应10行数据,所以上面的表省略了一部分以方便显示。A、B、C的元素和正文中是一样的。
key为A的行val全都是NULL, key为B的行中只有i=1的行val是3,其他的都是NULL, key为C的行val全部都是1。
请思考一下如何从这张表中选出val全是1的key。答案是C。
这次,我们要按“行方向”进行全称量化,所以使用EXISTS谓词。
严格来说,这个问题还是相当复杂的,如果能注意到问题在哪里,那你就是高级水平了。在使用EXISTS解答之后,请再试试看有没有别的解法。这个问题有很多种解法,非常有趣。
表SQL:
/* 练习题1-8-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);
1、错误解法
/* 练习题1-8-1:数组表——行结构表的情况 错误的结果 */ SELECT DISTINCT key1 FROM ArrayTbl2 AT1 WHERE NOT EXISTS (SELECT * FROM ArrayTbl2 AT2 WHERE AT1.key1 = AT2.key1 AND AT2.val <> 1);
2、exits解法
/* 正确解法 */ 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));
3、all谓词
/* 其他解法1:使用ALL谓词 */ SELECT DISTINCT key FROM ArrayTbl2 A1 WHERE 1 = ALL (SELECT val FROM ArrayTbl2 A2 WHERE A1.key = A2.key);
4、having子句
/* 其他解法2:使用HAVING子句 */ SELECT key FROM ArrayTbl2 GROUP BY key HAVING SUM(CASE WHEN val = 1 THEN 1 ELSE 0 END) = 10;
5、having子句2
/* 其他解法3:在HAVING子句中使用极值函数 */ SELECT key FROM ArrayTbl2 GROUP BY key HAVING MAX(val) = 1 AND MIN(val) = 1;
标签:INSERT,val,INTO,ArrayTbl2,VALUES,key,SQL,NULL From: https://www.cnblogs.com/stfzhuang/p/18142207