使用HQL/休眠的嵌套查询 [英] Nested query with HQL / Hibernate

问题描述

就我所读的内容而言，在某些情况下无法将子查询嵌套在HQL/休眠中.

For what I have read, in some cases it is not possible to nest subqueries in HQL / Hibernate.

比方说，我有一个价格表，属于一组.我们希望将它们全部添加，但是每组文章最多只能添加一个限制.实际上，该表是非规范化的，因此我们已经在商品表中拥有该组的最大数量.

Let's say that I have a table of articles with a price, that belong to a group. We want to add them all, but we can only add up to a limit per group of articles. Actually, the table is denormalized, so that we already have the maximum amount for the group in the article table.

因此，SQL会像这样简单:

So the SQL would be as simple as :

SELECT SUM(case when max_amount is null then price
when price<max_amount then price
else max_amount end)
FROM
(SELECT
SUM(price) as price, group_id, max_amount
FROM articles
GROUP BY group_id, max_amount
)

显然，我的数据模型更复杂，但这是我的主要问题.

Obviously, my data model is more complicated, but this is my main problem.

我想知道如何用HQL重写它，因为我想我不允许将一个子查询嵌套到另一个子查询中.

I wonder how can I rewrite this in HQL as I guess I am not allowed to nest one subquery into the other.

谢谢大家.

推荐答案

我不愿意以这种方式执行此操作，但是经过一些研究，我只能考虑按原样使用SQL查询.

I was reluctant to do this in this way, but after some research I could only think about using the SQL query as it is.

类似的东西:

String sQuery = "SELECT SUM(case when max_amount is null then price ...
final org.hibernate.SQLQuery query = session.createSQLQuery(sQuery);
List results = query.list();

我想不出使用HQL处理嵌套查询的Hibernate.

I can't think about Hibernate handling that nested query with HQL.

这篇关于使用HQL/休眠的嵌套查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！