求助!!SQL语句怎样翻译成HQL

1、sql语句:
SELECT
Mgr.id,
U.name AS applyname,
T.name,
U2.name AS frist_approvername,
U3.name AS second_approvername
FROM
mgr_business AS Mgr
LEFT JOIN mgr_business_type AS T ON Mgr.business_type_id = T.id
LEFT JOIN ac_user AS U ON Mgr.apply_user_id = U.id
LEFT JOIN ac_user AS U2 ON Mgr.first_approve_user_id = U2.id
LEFT JOIN ac_user AS U3 ON Mgr.second_approve_user_id = U3.id
2、变成Hql后:
SELECT
Mgr.Id,
U. NAME AS applyName,
Mgr.startDate,
Mgr.startdaypartId,
Mgr.endDate,
Mgr.enddaypartId,
T. NAME AS TypeName,
Mgr.businessstatusId,
Mgr.applyTime,
Mgr.applyRemark,
U2. NAME AS fristapprovername,
Mgr.firstapproverTime,
Mgr.firstapproverRemark,
U3. NAME AS secondapprovername,
Mgr.secondapproverTime,
Mgr.secondapproverRemark
FROM
TMgrBusiness AS Mgr
LEFT JOIN TType AS T ON Mgr.businesstypeId = T.Id
LEFT JOIN TAcUser AS U ON Mgr.applyuserId = U.Id
LEFT JOIN TAcUser AS U2 ON Mgr.firstapproveruserId = U2.Id
LEFT JOIN TAcUser AS U3 ON Mgr.secondapproveruserId = U3.Id

3、运行出错,网上说不能用JOIN ON ,请问该如何处理??谢谢!!

1个回答

那你就不join on呗。

这段是不join on的,

SELECT
Mgr.Id,
U. NAME AS applyName,
Mgr.startDate,
Mgr.startdaypartId,
Mgr.endDate,
Mgr.enddaypartId,
T. NAME AS TypeName,
Mgr.businessstatusId,
Mgr.applyTime,
Mgr.applyRemark,
U2. NAME AS fristapprovername,
Mgr.firstapproverTime,
Mgr.firstapproverRemark,
U3. NAME AS secondapprovername,
Mgr.secondapproverTime,
Mgr.secondapproverRemark
FROM
TMgrBusiness AS Mgr,
 TType AS T ,
TAcUser AS U , 
TAcUser AS U2 , 
TAcUser AS U3
where
Mgr.businesstypeId = T.Id
Mgr.applyuserId = U.Id
Mgr.firstapproveruserId = U2.Id
Mgr.secondapproveruserId = U3.Id

不过left join是左连接,如果没有则可为空,
所以用HQL的left join with

SELECT
Mgr.Id,
U. NAME AS applyName,
Mgr.startDate,
Mgr.startdaypartId,
Mgr.endDate,
Mgr.enddaypartId,
T. NAME AS TypeName,
Mgr.businessstatusId,
Mgr.applyTime,
Mgr.applyRemark,
U2. NAME AS fristapprovername,
Mgr.firstapproverTime,
Mgr.firstapproverRemark,
U3. NAME AS secondapprovername,
Mgr.secondapproverTime,
Mgr.secondapproverRemark
FROM
TMgrBusiness AS Mgr
LEFT JOIN TType AS T WITH Mgr.businesstypeId = T.Id
LEFT JOIN TAcUser AS U WITH Mgr.applyuserId = U.Id
LEFT JOIN TAcUser AS U2 WITH Mgr.firstapproveruserId = U2.Id
LEFT JOIN TAcUser AS U3 WITH Mgr.secondapproveruserId = U3.Id
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问