我使用SQL访问一个实体(Forslag),但也希望使用另一个实体的列(b.dato)进行排序。
这是我最初的SQL:
select distinct ff.*
from Forslag ff
inner join Forlag f on ff.forlag_id = f.forlag_id
inner join LoggBehandling b on ff.forlag_id = b.forlag_id
inner join Kontrollpanel p on f.uhrPuMote_id = p.saksbehandlerUhrPuMote_id
where b.status_id = 7
union all
select distinct ft.*
from Forslag ft
inner join Tidsskrift t on ft.tidsskrift_id = t.tidsskrift_id
inner join LoggBehandling b on ft.tidsskrift_id = b.tidsskrift_id
inner join Kontrollpanel p on t.uhrPuMote_id = p.saksbehandlerUhrPuMote_id
where b.status_id = 7
order by b.dato desc
Hibernate抱怨:ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
我怀疑我真的不能添加b.dato
来选择,因为我认为这会对映射产生影响:
select distinct ff.*, b.dato
from Forslag ff
inner join Forlag f on ff.forlag_id = f.forlag_id
inner join LoggBehandling b on ff.forlag_id = b.forlag_id
inner join Kontrollpanel p on f.uhrPuMote_id = p.saksbehandlerUhrPuMote_id
where b.status_id = 7
union all
select distinct ft.*, b.dato
from Forslag ft
inner join Tidsskrift t on ft.tidsskrift_id = t.tidsskrift_id
inner join LoggBehandling b on ft.tidsskrift_id = b.tidsskrift_id
inner join Kontrollpanel p on t.uhrPuMote_id = p.saksbehandlerUhrPuMote_id
where b.status_id = 7
order by b.dato desc
我应该如何解决这个问题?
您确定是HQL而不是SQL吗???
第一个:在HQL中,使用UNIONALL是不可能的,但必须执行两个不同的查询。
第二个:在SQL中,当您有UNION操作时使用order by时,您必须通过以下方式将order by应用于结果表:
第三:如果使用UNIONALL,为什么要使用DISTINCT??在不使用ALL的情况下使用UNION与DISTINCT是一样的。
select *
from (
select ff.*, b.dato as dato
from Forslag ff
inner join Forlag f on ff.forlag_id = f.forlag_id
inner join LoggBehandling b on ff.forlag_id = b.forlag_id
inner join Kontrollpanel p on f.uhrPuMote_id = p.saksbehandlerUhrPuMote_id
where b.status_id = 7
union all
select distinct ft.*, b.dato
from Forslag ft
inner join Tidsskrift t on ft.tidsskrift_id = t.tidsskrift_id
inner join LoggBehandling b on ft.tidsskrift_id = b.tidsskrift_id
inner join Kontrollpanel p on t.uhrPuMote_id = p.saksbehandlerUhrPuMote_id
where b.status_id = 7
) as resultTable
order by resultTable.dato desc