Question

java开发中，为什么我的sql语句在hibernate原生sql查询时查不到结果？

select MAX(CAST(x.serial as SIGNED))+1 as number from (select SUBSTRING(GOODS_ID,LOCATE('005001007',GOODS_ID)+LENGTH('005001007')) as serial from BUS_GOODS where ID like '%005001007%') x where x.serial <> ''
这是sql 我在mysql 客户端下把这个sql语句黏贴上去 能查到我想要的正确的结果 但是放入hibernate的原生sql查询接口里 就不行 怎么办？

Answer 1

select MAX(CAST(x.serial as SIGNED))+1 as number from (select SUBSTRING(GOODS_ID,LOCATE('005001007',GOODS_ID)+LENGTH('005001007')) as serial from BUS_GOODS where ID like '%005001007%') x where x.serial <> ''

你确定这句话在mysql里能跑？里面有两个where啊，而且第二个where前面还有个x是要闹哪样？

追问

不懂就算了 别为难自己了

Answer 2

你的x是后面的子查询的别名是吧，可是在最外层的select 时[select MAX(CAST(x.serial as SIGNED))+1 as number ]临时表的别名还没有创建