java - JPA Spring Repository 中的 Group By 之前的排序

Question

所以我的目标是针对一本特定的书(具有给定的 id)，以获取每种语言的已发布版本。如果没有发布的版本，那么我需要通过时间戳检索最新版本。

我需要做一个查询，在组之前执行 order by。

我知道在 sql 中你可以(大致)

Select * From 
( Select b from Book as b where b.author = ?1 ORDER BY (case when b.info.status=published then 1 else 2) asc, b.timestamp desc)
GroupBy book.language

但是我不知道如何使用 hibernate 在 jpa 存储库中执行类似的查询。我知道这行不通，因为订单因“in”而丢失

Select a From book a where a.id in  
( Select b from Book as b where b.author=?1 ORDER BY (case when b.info.status=published then 1 else 2) asc, b.timestamp desc)
GroupBy a.language

当它通过并 checkin 时，这意味着订单没有保留。无论如何在 jpa 存储库中执行此查询？

我不想为每个人做一个'for each'并获得发布的/最新的，因为这将非常低效

书籍设置为

书 |作者|信息编号

那么信息id有:

信息|语言|时间戳 |状态|…………

一个例子 目标是:得到 j.k.罗琳以各种语言出版/最新

书籍的示例设置是

book | author | info id
1    |Rowling |1 
2    |Rowling |2
3    |Rowling |3
4    |Rowling |4
5    |Tolkein |5

信息:

id|lang|ts | status
1 |en  |1  | published
2 |de  |5  | unpublished
3 |de  |3  | unpublished
4 |en  |9  | unpublished
5 |en  |4  | published

请求它会归还书1(已发布且为英文)和 2(如在 de 中，时间戳在 de 中最高)

Answer 1

不，这不行。 SQL 不适用于有序集，SQL(主要)适用于无序集。这意味着，每个查询、查询结果、子查询等都是一组无序的记录。

是的，这是因为这些集合在理论上正是您可以在高中学习的集合。

如果您执行GROUP BY、JOIN 或任何类似操作，则之前的ORDER BY 将失效。为什么？因为每个这样的关键字都在处理无序集。这也是为什么只有最后一个 SQL 查询可以有一个 ORDER BY 的原因。

如果这种内部排序以某种方式起作用，那只是某些 SQL 服务器的随机行为(旧的 MySQL 版本对此类解决方案特别好奇)。

你想要的，是

1. 按两列(published 和language)在记录中分组
2. ORDERING 也按这两列所需的记录。

所以:

选择 b
从书 b
WHERE author=?1 和 info.status=published
ORDER BY 发布，语言

...之类的。如果我能破译你想要什么，可能甚至不需要 group by。如果你扩展你的问题，你的目标是什么，我会用查询扩展我的答案。

分机#1:

它不能用基本的 SQL 来完成，它需要窗口函数。执行您想要的 SQL 查询:

SELECT *, ROW_NUMBER() OVER (ts PARTITION BY author, lang ORDER BY ts) AS tsn
从书 b
LEFT JOIN info i ON b.info_id=i.id
哪里 tsn=1

无论如何，您应该寻找 ROW_NUMBER() SQL 函数，并且您可能不需要 GROUP BY。

我还不知道如何将它移植到 hql(hibernate 查询语言)，但我很快会对此做一些研究。

分机#2:

Mysql 没有窗口函数或任何使 SQL 有用的东西。在古代是因为他们没有足够的程序员并且想要一个快速的系统，而不是一个聪明的系统。目前，这是因为 MySQL 归 Oracle 所有，他们想要免费并发到他们的 SQL 服务器。无论如何，MySQL 很糟糕，而且从长远来看，特别是如果你对 Java 足够好，我建议你使用一些更好的数据库(我认为你对 PostgreSQL 真的很满意)。

到此为止，这是一个 SQL 解决方案。这有点复杂。首先，我们得到每个作者、书籍和语言的最大时间戳:

SELECT book.author, book.id 作为 book_id, info.lang, MAX(info.ts) AS ts
从书中
LEFT JOIN info ON book.info_id=info.id
GROUP BY book.author, book.id, info.lang

我们将此查询称为 $queryMaxts。试试这个查询，它应该有效。

之后，我们可以将其连接回我们希望的表:

选择 *
FROM ($queryMaxts) 最大值
在 maxts.author=book.author 上左加入书
LEFT JOIN info ON maxts.lang=info.lang AND maxts.ts=info.ts AND book.info_id=info.id

...尽管它是一个纯 MySQL 解决方案并且与 JPA 无关。我建议以某种方式将它嵌入到 JPA 层中。

另一件重要的事情:

您几乎可以确定时间戳是唯一的。您可以通过创建唯一索引来保证这也使您的数据库更快:CREATE UNIQUE INDEX uniqts ON info(lang, ts)。