hibernate 用于计数和列表的复杂SQL查询

vojdkbi0 于 2022-11-14 发布在其他

关注(0)|答案(3)|浏览(146)

我需要编写复杂的SQL来计算不同名称的受欢迎程度，并按降序对它们进行排序。我将使用hibernate@Query(“SELECT*FROM...”)来完成这项工作。参数。首先，我试图在SQL上实现它，然后将其实现到我的Spring Boot项目中，但我在编写它时卡住了。

SELECT
    f.Name,
    COUNT(SELECT * FROM Family g WHERE g.Name WHERE ???) AS numberOfCount

FROM Family AS f
ORDER BY numberOfCount

id  | Name     | Surname | 
--------------------------
1   | John     | Smith   | 
2   | Mary     | Smith   | 
3   | John     | Dawson  | 
4   | Lisa     | Smith   | 
5   | Lisa     | Dawson  | 
6   | Jack     | Smith   |
7   | John     | Smith   |

排序版本：

Name    | Popularity
--------------------------
| John   |  3
| Lisa   |  2
| Mary   |  1
| Jack   |  1

Hibernate

来源：https://stackoverflow.com/questions/73619164/complex-sql-query-for-counting-and-listing

3条答案

按热度按时间

ljo96ir51#

我想您要的是group by。查询将如下所示：

SELECT name, COUNT(name) AS popularity 
FROM test 
GROUP BY NAME 
ORDER BY popularity DESC

Jpql应该是这样的：

@Query("SELECT t.name AS name, COUNT(t.name) AS popularity FROM Test t GROUP BY t.name ORDER BY popularity DESC")

赞(0）回复(0）举报 2022-11-14

x6yk4ghg2#

GROUP BY用于统计人气，DISTINCT用于唯一名称

select distinct name, count(id) as pop from test group by name order by pop desc;

赞(0）回复(0）举报 2022-11-14

u1ehiz5o3#

我想‘DISTINCT’会对你有帮助。
示例：

SELECT DISTINCT NAME, COUNT(ID) as ID_COUNT FROM TABLE GROUP BY NAME ORDER BY ID and use `asc` or `desc` as your requirement.

DISTINCT-将为您提供唯一的名称COUNT(ID)-将为您提供ID的计数。

赞(0）回复(0）举报 2022-11-14

我来回答

hibernate 用于计数和列表的复杂SQL查询

3条答案

相关问题

热门标签

最新问答