把查询条件嵌套在多个 left join 嵌套中使用时，无法在页面上展示查询信息

JackieXiangxy commented 1 month ago

版本号：

1.7.6

问题描述：

把查询条件嵌套在多个 left join 中使用时，无法在页面上展示查询信息 sql截图：

页面截图

错误日志&截图：

重现步骤：

select * from (
SELECT bpi.fsOutpatientName,pp.name creatorName,boc.createDate,bi.code,bi.name patientName,pd.name docDeptName,pp1.name docName,boci.Amount,
boci.preferential,boci.realAmount,boci.nowPay,boci.bankPay,boci.renderAccount,boci.insuranceRenderAccount+boci.socialSecurityPayment insuranceRenderAccount,boc.chargeTypeName,
ba1.cardNumber,brf.code regCode,bp.createDate prCreateDate,bp.code prCode,proj.name parenname,boci.unitName,excdept.name excdpname,
case when boci.Type='3' or (boci.Type='2' and boci.sourceId is not null) then 0 when boci.Type='2' and boci.sourceId is null then 0-boci.num else boci.num end num,excper.name excpername,brf.typeName,ifnull(sett.personCode,ba1.personNumber) personCode,
cartp.name cartpname,boci.name xmmc,case boci.Type when '1' then '收费' when '2' then '退费' when '3' then '本院' else '' end laiyuan,
pp2.name regPerson FROM b_outpatient_charge_item boci
LEFT JOIN b_outpatient_charge boc ON boc.id=boci.charge_id
LEFT JOIN b_prescription_item bpi ON bpi.id=boci.prescription_item_id
LEFT JOIN b_prescription bp ON bp.id=bpi.prescription_id
LEFT JOIN pl_person pp ON pp.id=boc.creator_id
LEFT JOIN b_invoice bi ON bi.id=boci.invoice_id
LEFT JOIN pl_department pd ON pd.id=bp.doctor_dept_id
LEFT JOIN pl_person pp1 ON pp1.id=bp.doctor_id
LEFT JOIN b_reg_from brf ON brf.id=boc.form_id
LEFT JOIN pl_person pp2 on brf.creator_id=pp2.id
LEFT JOIN b_account ba1 ON ba1.id=brf.account_id
LEFT JOIN b_project proj on boci.parentProjectId=proj.id
LEFT JOIN pl_department excdept on boci.excute_dept_id=excdept.id
LEFT JOIN pl_person excper on boci.excute_person_id=excper.id
LEFT JOIN pl_dict_item cartp on ba1.countType_id=cartp.id and cartp.dict_id='MzCls0ZlbFQiOcbu7Sn'
LEFT JOIN b_settling_medicalinsurance sett on boc.settlingMedicalInsurance_id=sett.id
LEFT JOIN (select  a.sourceId,abs(a.renderAccount) renderAccount from b_outpatient_charge_item a 
where a.type='3' and a.sourceId is not null 
<#if isNotEmpty(startTime)> 
 and a.date >= '${startTime}'
</#if>
<#if isNotEmpty(endTime)> 
 and a.date <= '${endTime}'
</#if>

 GROUP BY a.sourceId ) rend 
on rend.sourceId=boci.id
WHERE 1=1 
<#if isNotEmpty(startTime)> 
 and boc.createDate >= '${startTime}'
</#if>
<#if isNotEmpty(endTime)> 
 and boc.createDate <= '${endTime}'
</#if>
<#if isNotEmpty(klx)> 
  and ba1.countType_id= '${klx}'
</#if>
<#if isNotEmpty(xiangmu)> 
 and boci.name like concat('%','${xiangmu}','%')
</#if>
<#if isNotEmpty(huanzhe)> 
 and bi.name like concat('%','${huanzhe}','%')
</#if>
<#if isNotEmpty(keshi)> 
  and bp.doctor_dept_id = '${keshi}'
</#if>
<#if isNotEmpty(yisheng)> 
  and bp.doctor_id= '${yisheng}'
</#if>

union all
SELECT '外院报销' fsOutpatientName,b.name creatorName,a.createDate,'' code,a.userName patientName,'' docDeptName,'' docName,a.realAmountTotal Amount,
a.realAmountTotal preferential,0 realAmount,a.nowPay,a.bankPay,a.renderAccountTotal renderAccount,0 insuranceRenderAccount,'外院报销' chargeTypeName,
c.cardNumber,'' regCode,'' prCreateDate,'' prCode,'' parenname,'笔' unitName,'' excdpname,
case a.Type when '2' then -1 else 1 end num,'' excpername,'院外就诊' typeName,c.personNumber personCode,
d.name cartpname,'大学生外院报销' xmmc,'外院' laiyuan,
'' regPerson
from b_outpatient_charge a 
LEFT JOIN pl_person b on a.creator_id=b.id
LEFT JOIN b_account c on a.account_id=c.id
LEFT JOIN pl_dict_item d on c.countType_id=d.id and d.dict_id='MzCls0ZlbFQiOcbu7Sn'
where a.outerCourt=1

<#if isNotEmpty(startTime)> 
 and a.createDate >= '${startTime}'
</#if>
<#if isNotEmpty(endTime)> 
 and a.createDate <= '${endTime}'
</#if>
<#if isNotEmpty(klx)> 
  and c.countType_id= '${klx}'
</#if>
<#if isNotEmpty(xiangmu)> 
 and '大学生外院报销' like concat('%','${xiangmu}','%')
</#if>
<#if isNotEmpty(huanzhe)> 
 and a.userName  like concat('%','${huanzhe}','%')
</#if>

) as temp ORDER BY temp.createDate