我需要撰寫復雜的 sql 來計算不同名稱的流行度并按降序對它們進行排序。我將使用休眠 @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
uj5u.com熱心網友回復：
我認為您正在尋找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")
uj5u.com熱心網友回復：
您可以使用group by來計算受歡迎程度，并使用distinct來計算唯一名稱
select distinct name, count(id) as pop from test group by name order by pop desc;
uj5u.com熱心網友回復：
我認為'DISTINCT'會幫助你。
例子 -
從表中選擇不同的名稱
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/505371.html