在带有“重复列名"的H2数据库上，带有子查询的COUNT失败. [英] COUNT with subquery fail on H2 database with "Duplicate column name"

问题描述

当使用子查询查询COUNT时，我们意识到H2的异常行为.

we aware strange behavior of H2 when querying COUNT with a subquery.

准备表格:

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

测试简单查询(正常工作):

Test simple query (works fine):

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

使用计数测试同一查询:

Test same query with count:

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语句: 选择count(*)FROM(

[42S21][42121] Duplicate column name "ID"; SQL statement: select count(*) FROM (

有什么解决方法吗?

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

UPDATED: The problem was in that original table name erased when preparing subquery column names and in fact I have :

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

添加别名(如 Abdul Rasheed 所述)解决了该问题.

Adding alias (as mentioned by Abdul Rasheed) fix the problem.

推荐答案

我不明白为什么要使用相同的字段和相同的表作为别名. 为什么不简单地使用这样的查询:

I don't understand why to use the same field and the same table with aliases. Why don't you use simply a query like that :

SELECT count(id) FROM Foo

这篇关于在带有“重复列名"的H2数据库上，带有子查询的COUNT失败.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！