关于spring data jpa 自定义sql多表查询的sql解析异常问题

最近在学习spring data jpa ,发现spring data jpa虽然也支持自定义sql的本地查询,但是对超级长的sql支持的不是很好,特别是自己写的含有from 重命名的sql,虽然sql在数据库可以执行,但编译的时候却不通过,比如以下这条长sql:

select f.str_FlightNumber as flightNumber,f.str_ExecDate as execDate,f.i_SecurityCheckStatus as securityCheckStatus,f.i_BoardingStatus as boardingStatus,
count(distinct p.i_Id) as departureNumber,//统计离港人数
(select count(1) from t_passengerinfo where i_BoardingStatus = 2 and str_FlightNumber = str_Flight and str_ExecDate = str_Date and str_BelongAirportThreeCode = str_Group) as checkedNumber,//本次航班已安检人数
(select count(1) from t_passengerinfo where i_CheckStatus = 1 and str_FlightNumber = str_Flight and str_ExecDate = str_Date and str_BelongAirportThreeCode = str_Group) as boardingNumber//本次航班已登机人数
from t_flightplan f, t_passengerinfo p
where f.str_FlightNumber = p.str_Flight and f.str_ExecDate = p.str_Date and f.str_BelongAirportThreeCode = p.str_Group
group by f.i_Id

 

这是查询航班表,乘客表,统计本次航班已登机的乘客数,统计本次航班已安检的乘客数,统计本次航班离港人数,然后按航班id分组

这条sql在数据库里执行是没有问题的,但是被spring data jpa 解析的时候就各种毛病,反正就是编译失败,我查找了N篇博客都没找到问题所在,难道是SQL太长了它看不懂???

然后我在数据库创建了一个视图:

create view v_flightdynamic as 
select f.str_FlightNumber as flightNumber,f.str_ExecDate as execDate,f.i_SecurityCheckStatus as securityCheckStatus,f.i_BoardingStatus as boardingStatus,
count(distinct p.i_Id) as departureNumber,
(select count(1) from t_passengerinfo where i_BoardingStatus = 2 and str_FlightNumber = str_Flight and str_ExecDate = str_Date and str_BelongAirportThreeCode = str_Group) as checkedNumber,
(select count(1) from t_passengerinfo where i_CheckStatus = 1 and str_FlightNumber = str_Flight and str_ExecDate = str_Date and str_BelongAirportThreeCode = str_Group) as boardingNumber
from t_flightplan f, t_passengerinfo p
where f.str_FlightNumber = p.str_Flight and f.str_ExecDate = p.str_Date and f.str_BelongAirportThreeCode = p.str_Group
group by f.i_Id

在我原本的sql位置:

@Query(value = "select * from v_flightdynamic",nativeQuery = true)
    Page<Object[]> getFlightDynamics(Pageable pageable);

emm  完美通过 美滋滋。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值