问题描述
我正在查询一个维护不佳的建筑物数据库,该数据库的记录类似于以下内容:
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的重复记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!