jpa - JPA 中的 ORDER BY "CASE column"

Question

我想按 case 语句排序，这可能吗？我该怎么做？

SELECT new com.systemname.to.UserDataHolder(u.userId, 
CASE  WHEN (u.place.cityId = :cityId) THEN 1  WHEN (u.place.stateId = :stateId) THEN 2  ELSE 3 END)  
FROM User u 
ORDER BY u.userId DESC 

Answer 1

您使用的是什么 JPA 提供程序？

尝试，

SELECT u.userId, 
(CASE  
    WHEN (u.place.cityId = :cityId) THEN 1
    WHEN (u.place.stateId = :stateId) THEN 2
    ELSE 3 END) as myNum
FROM User u 
ORDER BY u.userId, myNum DESC

或者，

SELECT new com.systemname.to.UserDataHolder(u.userId, 
CASE  
    WHEN (u.place.cityId = :cityId) THEN 1
    WHEN (u.place.stateId = :stateId) THEN 2
    ELSE 3 END)
FROM User u 
ORDER BY u.userId, CASE  
    WHEN (u.place.cityId = :cityId) THEN 1
    WHEN (u.place.stateId = :stateId) THEN 2
    ELSE 3 END DESC