hsqldb - 使用 HSQL 和 WHERE 语句时的问题

Question

在 HSQL 中使用 WHERE 语句时出现问题。当我输入如下代码时，我没有收到任何错误：

"SELECT Year, ProjectName, (cast(sum(Missed) as float)/(select cast(sum(d.Missed) as float) from Draft d)) as MissedPer from Draft group by ProjectName, Year order by ProjectName"

但是，当我在嵌套查询中添加 WHERE 语句时，出现错误：

"SELECT Year, ProjectName, (cast(sum(Missed) as float)/(select cast(sum(d.Missed) as float) from Draft d where Year = '19.12')) as MissedPer from Draft group by ProjectName, Year order by ProjectName"

错误如下：

线程“main”中的异常 java.sql.SQLSyntaxErrorException：表达式不在聚合或 GROUP BY 列中：语句 [SELECT BranchNo, ProjectName, ( cast(sum(Missed) as float)/(select cast(sum(d.Missed) as float) from Draft d where d.BranchNo = '19.12')) as MissedPer from Draft group by ProjectName, BranchNo order by ProjectName]

我不知道如何使这项工作，请帮助。

标签： hsqldb

Answer 1

我检查了 HSQLDB 版本 2.5.1，查询返回结果：

create table draft (year varchar(10), projectname varchar (10), missed int);
insert into draft values '19.10', 'p alpha', 10
insert into draft values '19.11', 'p alpha', 12
insert into draft values '19.12', 'p alpha', 3


YEAR  PROJECTNAME MISSEDPER          
----- ----------- ------------------ 
19.10 p alpha     3.3333333333333335 
19.11 p alpha     4.0                
19.12 p alpha     1.0