带有子查询的 COUNT 在具有"Duplicate column name"的 H2 数据库上失败

Question

我们意识到 H2 在使用子查询 COUNT 时的奇怪行为。

准备表：

CREATE TABLE Foo
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  fieldName VARCHAR(30) NOT NULL,
);

测试简单查询(工作正常(：

SELECT F1.id, F2.id from Foo as F1 INNER JOIN Foo F2 on F1.id = F2.id

使用计数测试相同的查询：

SELECT count(*) FROM (
  SELECT F1.id, F2.id from Foo as F1 INNER JOIN Foo F2 on F1.id = F2.id
) q;

出现以下错误：

[

42S21][42121] 重复的列名"ID";SQL语句： 从 (

有什么解决方法吗？

更新：问题在于准备子查询列名称时删除了原始表名，实际上我有：

SELECT count(*) FROM (
  SELECT id, id from q
); 

添加别名(如阿卜杜勒·拉希德所述(可以解决问题。

Answer 1

我不明白为什么要使用相同的字段和带有别名的同一表。你为什么不简单地使用这样的查询：

SELECT count(id) FROM Foo

Answer 2

此问题与 H2 无关，但发生在所有 SQL 数据库中。允许顶级 SQL SELECT 语句投影同名的两列，但不允许子查询(特别是派生表(。这与是否引用子查询中的不明确列名无关。

解决方案是显式

别名列：

SELECT count(*) FROM (
  SELECT f1.id AS f1_id, f2.id AS f2_id 
  FROM foo AS f1 
  INNER JOIN foo f2 ON f1.id = f2.id
) q;

或者完全避免派生表中的投影，因为您不需要它来计算计数值：

SELECT count(*) FROM (
  SELECT 1
  FROM foo AS f1 
  INNER JOIN foo f2 ON f1.id = f2.id
) q;

此时，您显然可以完全避免派生表：

SELECT count(*) 
FROM foo AS f1 
INNER JOIN foo f2 ON f1.id = f2.id;

使用

H2 1.4.198 或更高版本时的替代方法是使用窗口函数计算每行的计数值：

SELECT f1.id AS f1_id, f2.id AS f2_id, count(*) OVER ()
FROM foo AS f1 
INNER JOIN foo f2 ON f1.id = f2.id