在JPA Spring库中按前按组订购

Question

因此，我的目标是编写一本特定的书(带有给定的id)，以获得每种语言的出版版本。如果没有发布的版本，那么我需要通过时间戳检索最近的版本。

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

我知道在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

当它通过并签入时，这意味着订单没有得到遵守。在jpa存储库中是否存在这样的查询？

我不想做一个‘为每个人’，并获得发布/最新的，因为这将是非常低的效率

这些书被设置为

图书作者信息id

然后信息id有：

信息infoid\语言\\时间戳/状态\.

的一个例子是的目标是:获取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的原因。

如果这种内部排序工作正常，那么它只是一些servers的一种随机行为(旧的MySQL版本在这种解决方案中特别好奇)。

你想要的是

1. 分组-在记录中按两列(published和language)
2. 通过这两列对所需的记录进行排序。

所以：

SELECT b FROM Book b WHERE author=?1 AND info.status=published ORDER BY published, language

...or就这样。如果我能破译你想要什么，可能连一个group by都不需要。如果您扩展您的问题，您的目标是什么，我将扩展我的回答与查询。

分机#1：

它不能用基本的SQL来完成，因为它需要窗口函数。一个SQL查询，它可以执行您想要的操作：

SELECT *, ROW_NUMBER() OVER (ts PARTITION BY author, lang ORDER BY ts) AS tsn FROM Book b LEFT JOIN info i ON b.info_id=i.id WHERE tsn=1

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

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

分机2：

Mysql没有窗口功能或任何使SQL有用的功能。在古代，这是因为他们没有足够的程序员，他们想要一个快速的系统，而不是一个智能的系统。目前，这是因为MySQL归甲骨文所有，他们想要一个免费的并发到他们的server。无论如何，MySQL是不好的，从长远来看，特别是如果您对Java已经足够好的话，我建议您使用一些更好的DB (我认为，对于PostgreSQL，您真的很满意)。

在此之前，这里有一个SQL解决方案。有点复杂。首先，我们得到了每个作者、书籍和语言的最大时间戳：

SELECT book.author, book.id as book_id, info.lang, MAX(info.ts) AS ts FROM book LEFT JOIN info ON book.info_id=info.id GROUP BY book.author, book.id, info.lang

我们将此查询称为$queryMaxts。尝试这个查询，它应该可以工作。

在此之后，我们可以将其加入到我们希望的表中：

SELECT * FROM ($queryMaxts) maxts LEFT JOIN book ON maxts.author=book.author LEFT JOIN info ON maxts.lang=info.lang AND maxts.ts=info.ts AND book.info_id=info.id

...although --它是一个纯MySQL解决方案，对JPA没有什么可做的。我建议以某种方式将其嵌入JPA层。

另一件重要的事情是：

您几乎可以确定时间戳是唯一的。通过创建一个惟一的索引：CREATE UNIQUE INDEX uniqts ON info(lang, ts)，您也可以保证数据库运行速度更快。