java - 简单但复杂的 HQL/SQL 查询

Question

我有两个表，一对多关系(测验，评论):一个测验可以有多个评论

我需要在页面内显示每个测验的最后 5 条评论。

是否可以使用一个查询来提取这些评论(如果不能，最好的方法是什么)？现在我正在为每个测验执行单独的查询以提取最后 5 条评论。

(我希望找到一个单一的 HQL 来允许我提取这些评论)

附言。我正在使用 hibernate/jpa/mysql

Answer 1

我写了一个与 MySQL 一起运行的复杂 SQL ;-)
基本思想是:

• 对评论进行排序并添加 rownum 作为排名。对同一测验的评论生成了具有连续数字的排名
• 加入每个测验的最新评论的最大聚合
• 有了这些附加信息，就可以构建一个 where 子句来限制每个测验的评论

先决条件是评论有较新的date有更高的id

更新:更改了 SQL。 (在我的小测试数据集中实现了一些缺失的测试用例)
这部分SQL后来被使用了两次......你应该用它创建一个 View 。

SELECT 
  @rownum:=@rownum+1 AS Rank,
  c.*
FROM _comments c, (SELECT @rownum:=0) r
ORDER BY  c.q_id, c.id

-

SELECT 
  ranked_c.*
FROM (
    SELECT 
      @rownum:=@rownum+1 AS Rank,
      c.*
    FROM _comments c, (SELECT @rownum:=0) r
    ORDER BY  c.q_id, c.id
  ) ranked_c
  INNER JOIN (
    SELECT 
      i.q_id,
      MAX(i.Rank) AS LastEntry_id
    FROM  (
      SELECT 
        @rownum:=@rownum+1 AS Rank,
        c.*
      FROM _comments c, (SELECT @rownum:=0) r
      ORDER BY c.q_id, c.id
    ) i
    GROUP BY i.q_id
  ) max_c ON ranked_c.q_id = max_c.q_id
WHERE max_c.LastEntry_id - ranked_c.Rank BETWEEN 0 AND 4 

替代 where 子句:abs(max_c.LastEntry_id - ranked_c.Rank) < 5

--> 使用 View 的解决方案:

CREATE OR REPLACE VIEW V_RankedComments AS (
  SELECT 
    @rownum:=@rownum+1 AS Rank,
    c.*
  FROM _comments c, (SELECT @rownum:=0) r
  ORDER BY c.q_id, c.id
)
SELECT 
  ranked_c.*
FROM V_RankedComments ranked_c
  INNER JOIN (
    SELECT 
      i.q_id,
      MAX(i.Rank) AS LastEntry_id
    FROM V_RankedComments i
    GROUP BY i.q_id
  ) max_c ON ranked_c.q_id = max_c.q_id
WHERE max_c.LastEntry_id - ranked_c.Rank BETWEEN 0 AND 4