hibernate的使用过程中,遇到了很多hibernate语句产生的问题。
select min(max(case_numnum)) + 1 case_numnum
from
(select t.case_numnum,t.case_numnum - row_number() over(order by t.case_numnum) diff
from xzss_info_new t
where t.case_org='0000' and t.case_numyear='2017')
group by diff;
对于以上的代码,使用了row_number() over来进行数据的排序,hibernate不提供支持。
select min(max(case_numnum)) + 1 case_numNum
from (select case_numnum,(case_numnum-rownum) diff
from (select t.case_numnum
from xzss_info_new t where t.case_org='0000' and t.case_numyear='2017' order by t.case_numnum asc))
group by diff
于是改成了这种形式,然后发现hibernate只支持where的嵌套子查询。
select case_numnum,(case_numnum-rownum) diff
from xzss_info_new
where id in
(select t.id
from xzss_info_new t where t.case_org='0000' and t.case_numyear='2017' )
本想改成where嵌套,但修改内部的子查询时,发现in 无法和子查询的order by同时使用。
可以解决的方案有几种:
1、使用视图
2、使用sql查询
3、hibernate自身创建视图替代sql的形式https://wenku.baidu.com/view/12383f0df12d2af90242e6cf.html