JPQL / SQL：如何从单列上具有分组依据的表中选择*？

发布于 2024-08-30 07:24:49 字数 469 浏览 5 评论 0原文

我想选择表的每一列，但希望在行的单个属性上具有不同的值（示例中的城市）。我不需要额外的列，例如计数或任何其他列，只需要有限数量的结果，而且似乎不可能直接在 JPQL 查询中限制结果。

原始表：

ID    |   Name   |   City
---------------------------
1     |   John   |   NY
2     |   Maria  |   LA
3     |   John   |   LA
4     |   Albert |   NY

如果我在城市上做不同的事情，想要的结果：

ID    |   Name   |   City
---------------------------
1     |   John   |   NY
2     |   Maria  |   LA

最好的方法是什么？感谢您的帮助。

原文

I would like to select every column of a table, but want to have distinct values on a single attribute of my rows (City in the example). I don't want extra columns like counts or anything, just a limited number of results, and it seems like it is not possible to directly LIMIT results in a JPQL query.

Original table:

ID    |   Name   |   City
---------------------------
1     |   John   |   NY
2     |   Maria  |   LA
3     |   John   |   LA
4     |   Albert |   NY

Wanted result, if I do the distinct on the City:

ID    |   Name   |   City
---------------------------
1     |   John   |   NY
2     |   Maria  |   LA

What is the best way to do that? Thank you for your help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

逆流 2024-09-06 07:24:49

在 JPQL 中，您可以执行以下操作：

select e 
from MyEntity e 
where e.id in (select min(e.id) from MyEntity e group by e.city)

这会返回：

MyEntity [id=1, name=John, city=NY]
MyEntity [id=2, name=Maria, city=LA]

In JPQL, you could do something like this:

select e 
from MyEntity e 
where e.id in (select min(e.id) from MyEntity e group by e.city)

This returns:

MyEntity [id=1, name=John, city=NY]
MyEntity [id=2, name=Maria, city=LA]

回复收藏 0 原文

依靠 2024-09-06 07:24:49

不知道 JPQL，但 SQL 是：

SELECT x.*
  FROM TABLE x
  JOIN (SELECT MIN(t.id) AS min_id,
               t.city
          FROM TABLE t
      GROUP BY t.city) y ON y.min_id = x.id
                        AND y.city = x.city

Dunno about JPQL, but the SQL would be:

SELECT x.*
  FROM TABLE x
  JOIN (SELECT MIN(t.id) AS min_id,
               t.city
          FROM TABLE t
      GROUP BY t.city) y ON y.min_id = x.id
                        AND y.city = x.city

回复收藏 0 原文

~没有更多了~