一个神奇的SQL聚合查询案例

今天给大家分享一个 SQL 案例，假如存在以下两个表：

CREATE TABLE t1 (val1 INT);
INSERT INTO t1 VALUES (1), (2), (3);

CREATE TABLE t2 (val2 INT);
INSERT INTO t2 VALUES (10), (20), (30);

每个表包含 3 条数据。请问，以下查询返回结果是什么？

SELECT (SELECT sum(val1) 
        FROM t2 
        LIMIT 1) res
FROM t1;

不同数据库对于上面的查询实现并不一致，大体可以分为两种。

实现方法一

对于 MySQL、SQL Server、PostgreSQL 以及 SQLite 而言，查询结果如下：

res|
---+
  6|

它们的实现逻辑如下：

SELECT (SELECT sum_a
        FROM t2
        LIMIT 1
       )
  FROM (SELECT sum(val1) AS sum_a
        FROM t1
       ) tmp;

也就是说，子查询中的 sum(val1) 是外部查询的结果。先执行以下外部查询：

SELECT sum(val1) AS sum_val1
FROM t1;

sum_val1|
--------+
       6|

然后执行以下子查询：

SELECT 6 res
FROM t2
LIMIT 1;

所以查询最终返回了一个 6。

如果我们把子查询中的 LIMIT 去掉，将会返回以下错误：

SQL 错误 [1242] [21000]: Subquery returns more than 1 row

这也说明了 sum 函数是在外部查询中执行，因为以下聚合函数不会返回多条数据：

SELECT sum(expression) 
FROM t2

实现方法二

对于另外一些数据库而言，例如 Oracle，查询结果如下：

SELECT (SELECT sum(val1) 
        FROM t2
        FETCH FIRST 1 ROWS ONLY) res
FROM t1;

RES|
---+
  3|
  6|
  9|

它的实现逻辑是先执行外部查询 t1，返回 3 条数据（1，2，3）。然后将结果传递给子查询，计算 sum，最终返回了 3 条数据。

查询中的 FETCH FIRST 1 ROWS ONLY 可以省略，因为聚合查询只会返回一条数据。

另外一个查询语句

我们再考虑一种情况，以下查询语句应该返回什么结果：

SELECT (SELECT sum(val1+val2) 
        FROM t2 
        LIMIT 1) res
FROM t1;

子查询中的 sum 函数同时使用了 t1 和 t2 的字段。

此时，MySQL、Oracle、PostgreSQL 以及 SQLite 都会返回 3 条记录：

res|
---+
 63|
 66|
 69|

它们的实现逻辑回到了先执行外部查询 t1，返回 3 条数据（1，2，3）。然后将结果传递给子查询，计算 sum，最终返回了 3 条数据。此时，我们可以将语句中的 LIMIT 去掉，不会影响结果。

对于 SQL Server，以上语句回返回错误：

SQL 错误 [8124] [S0001]: 在包含外部引用的被聚合表达式中指定了多个列。如果被聚合的表达式包含外部引用，那么该外部引用就必须是该表达式中所引用的唯一的一列。

也就是说，它不支持聚合函数既引用内部表，又引用外部表。