SQL题目：在数据中找出所有val全为1的key

这道题目我觉得很经典，所以记录下来。

表的结果如下：

一个实体对应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;