使用JPQL过滤具有唯一ID的重复记录 [英] Filtering duplicate records that have a unique ID using JPQL

问题描述

我正在查询一个维护不佳的建筑物数据库，该数据库的记录类似于以下内容:

I am querying a badly maintained buildings database that has records similar to the following:

ID    NAME          CODE
54    Building A    a1234
97    Building A    a1234

我正在使用以下JPQL语句进行查询(其中"bCodes"是建筑代码数组):

I am querying using the following JPQL statement (where "bCodes" is an array of building codes):

SELECT building FROM Building building WHERE building.NAME IN (:bCodes)

如预期的那样，如果bCodes ="a1234"，我将同时获得两条记录. JPQL中是否有一种方法只能拉回其中一条记录?到目前为止，我找不到任何方法-在这种情况下，GROUP BY和DISTINCT似乎无法正常工作.

As expected, I get back both records if bCodes = "a1234". Is there a way in JPQL to only pull back one of the records? I can't find a way so far - it seems that GROUP BY and DISTINCT won't work in this context.

推荐答案

我建议修复数据库(删除重复项并在适当的列上添加唯一约束).但是，要回答您的问题，您可以使用选择任意ID的子查询进一步过滤查询:

I would recommend fixing your database (remove duplicates and put a unique constraint on the appropriate columns). However, to answer your question, you could further filter your query with a subquery that picks an arbitrary id:

SELECT building FROM Building building 
WHERE building.code IN :bCodes
AND building.id = (SELECT MIN(b2.id) FROM Building b2 
                   WHERE b2.name = building.name 
                   AND b2.code = building.code)

这篇关于使用JPQL过滤具有唯一ID的重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！