连接中的HQL子查询 [英] HQL Subqueries in joins

问题描述

我有一个Order类，它包含一组Order Order，它本身包含一组OrderItem's。

在SQL中，可以在JOIN子句中使用SELECT语句如下面的查询所做的那样：

  SELECT 
 o.id，
 o.amount，
 sum（s.quantity * s.price），
 sum（s.quantity * i.price）
 FROM 
 orders AS o 
 JOIN订单号AS s ON s。 order_id = o.id 
 JOIN（SELECT section_id，sum（quantity * price）AS price FROM orderitems GROUP BY section_id）AS i ON i.section_id = s.id 
 GROUP BY o.id，o。金额
  

是否可以在HQL中表达这样的查询？

 >  SELECT 
 o.id，
 o.amount，
 sum（s.quantity * s.price），
 sum（s.quantity * i .quantity * i.price）
 FROM订单AS 
 JOIN订单在AS s.or上der_id = o.id 
 JOIN orderitems AS i ON i.section_id = s.id 
 GROUP BY o.id，o.amount 
  

在这种情况下，它可以在HQL中重写为：

  SELECT 
 o.id，
 o.amount，
 sum（s.quantity * s.price），
 sum（s.quantity * i.quantity * i.price）
 FROM订单AS o 
 JOIN o.sections AS s 
 JOIN s.items AS i 
 GROUP BY o.id，o.amount 
  

如果我缺少某些东西，并且上面的查询没有返回您想要的内容，那么您的HQL转换运气不佳，因为 HQL中的子查询只能出现在SELECT或WHERE子句中。但是，您可以将您的查询映射为< sql-query> - 最终应该没有区别。

I have a class Order that contains a set of OrderSection's that itself contains a set of OrderItem's.

In SQL, one can use a SELECT statement in a JOIN clause as done in the following query:

SELECT
    o.id,
    o.amount,
    sum(s.quantity*s.price),
    sum(s.quantity*i.price)
FROM
    orders AS o
    JOIN ordersections AS s ON s.order_id=o.id
    JOIN (SELECT section_id, sum(quantity*price) AS price FROM orderitems GROUP BY section_id) AS i ON i.section_id=s.id
GROUP BY o.id, o.amount

Would it be possible to express such a query in HQL?

解决方案

Unless I'm missing something, your query can be rewritten in SQL as:

SELECT
  o.id,
  o.amount,
  sum(s.quantity*s.price),
  sum(s.quantity*i.quantity*i.price)
FROM orders AS o
  JOIN ordersections AS s ON s.order_id=o.id
  JOIN orderitems AS i ON i.section_id=s.id
GROUP BY o.id, o.amount

In which case it can then be rewritten in HQL as:

SELECT
  o.id,
  o.amount,
  sum(s.quantity*s.price),
  sum(s.quantity*i.quantity*i.price)
FROM orders AS o
  JOIN o.sections AS s
  JOIN s.items AS i
GROUP BY o.id, o.amount

If I am missing something and the above query does not return what you want, you're out of luck with HQL conversion because Subqueries in HQL can only occur in SELECT or WHERE clauses. You can, however, map your query as <sql-query> - should make no difference at the end.

这篇关于连接中的HQL子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！