我正在尝试对视图中的一列(PrefId
(执行加权排序。为此我遇到了Order By FIELD
。
这是我的确切查询:
select
e.employeeName as empName,
e.employeeLogin as empAlias,
e.managerName as mangerName,
e.managerLogin as managerAlias,
b.buildingName as buildingName,
b.country as country,
b.region as region,
CASE WHEN w.preferenceId IS NULL THEN 0 ELSE w.preferenceId END as PrefId,
CASE WHEN w.lastUpdatedDate IS NULL THEN e.lastUpdatedDate ELSE w.lastUpdatedDate END as updateDate
FROM employee_details e
INNER JOIN buildings_details as b ON e.building = b.building
LEFT JOIN workplace_details w ON e.employeeId = w.employeeId
WHERE e.isWorkplacePrefScopeIncluded = 1
ORDER BY FIELD(PrefId, 4, 3, 99, 2, 1, 0);
查询在我的工作台上运行良好,结果如预期,但在通过hibernate执行相同操作时,会给出
org.hibernate.engine.jdbc.spi.SqlExceptionHelper: Unknown column 'PrefId' in 'order clause'
我也尝试过使用-ORDER BY FIELD(w.preferenceId, 4, 3, 99, 2, 1, 0)
,但这似乎不符合加权排序顺序。
我哪里错了??
实际上您似乎有两个问题。首先,PrefId
是一个别名,它是您在查询前面的select子句中定义的。Hibernate似乎对此颇有怨言,所以只需使用preferenceId
即可。其次,FIELD
是MySQL扩展,不是ANSI标准的一部分,也不是Hibernate查询语言的一部分。然而,您可以重构ORDER BY
子句:
ORDER BY FIELD(PrefId, 4, 3, 99, 2, 1, 0);
为此,使用CASE
表达式:
ORDER BY
CASE COALESCE(w.preferenceId, 0) WHEN 4 THEN 1
WHEN 3 THEN 2
WHEN 99 THEN 3
WHEN 2 THEN 4
WHEN 1 THEN 5
WHEN 0 THEN 6
ELSE 7 END