英文:
Query group by in h2 works but not in Oracle DB
问题
我有一个类似以下的查询：
SELECT
sum(field1) as t1.sum_f1,
(select count(*) from table2) as count_t2,
t1.f2
FROM
t1
GROUP BY
t1.f2
这个查询是由javax.persistence.EntityManager Java实例执行的。
EntityManager将查询在Oracle数据库中运行。
我编写了使用H2内存数据库配置的Mockito测试。
问题如下：
当相同的查询由Mockito测试（使用H2作为数据库）执行时，它正常工作。
当我运行使用OracleDB作为数据库的Java应用程序时，它给我返回错误：ORA-00979。
我认为它希望在分组字段列表中包含count_t2，但这是不允许的，因为内部查询在分组之后处理（因此count_t2在SQL中的“分组时”不可见）。
我该如何解决？
谢谢。
英文:
i have a query like the following:
SELECT
sum(field1) as t1.sum_f1,
(select count(*) from table2) as count_t2,
t1.f2
FROM
t1
GROUP BY
t1.f2
This query is getting executed by a javax.persistence.EntityManager java istance.
EntityManager runs the query in an Oracle DB.
I've written Mockito test, configured with H2 in memory database.
The problem is the following:
When the same query is getting executed by a Mockito test (that uses H2 as DB), it works fine.
When i run my java application (that uses OracleDB as DB) it gives me ERROR: ORA-00979.
I think it expects count_t2 to be in the group by field list but this is not permitted cause inner queries are processed after gruop by (so count_t2 is not visible at "group by - time") in SQL.
How can i solve it?
Thanks.
答案1
得分: 1
将计算移到“FROM”子句：
SELECT SUM(field1) as t1.sum_f1, t2.count_t2, t1.f2
FROM t1 CROSS JOIN
(SELECT COUNT(*) as count_t2 FROM table2) t2
GROUP BY t1.f2, t2.count_t2;
具有聚合查询的子查询可能对查询解析器造成困扰。
英文:
Move the calculation to the FROM clause:
SELECT SUM(field1) as t1.sum_f1, t2.count_t2, t1.f2
FROM t1 CROSS JOIN
(SELECT COUNT(*) as count_t2 FROM table2) t2
GROUP BY t1.f2, t2.count_t2;
Subqueries with aggregation queries can be tricky for query parsers.
答案2
得分: 0
我终于找到了让它工作的方法。
选择
求和(field1) 作为 t1.sum_f1，
计数((选择 1 从 table2)) 作为 count_t2，
t1.f2
从
t1
分组依据
t1.f2
通过这种方式，投影字段为 "SUM, COUNT" 和聚合字段。
这个查询在 H2 和 ORACLE 数据库中都正常工作。
英文:
I finally found the way to let it works.
SELECT
sum(field1) as t1.sum_f1,
count((select 1 from table2)) as count_t2,
t1.f2
FROM
t1
GROUP BY
t1.f2
In this way projection fields are "SUM, COUNT" and the aggregation field.
This query works fine in both H2 and ORACLE DB.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论