主题:如何动态构造列名

zhoyg 2018年02月12日 212

我有一个需求是动态构造列名,收入+业务种类编号 ,其中业务种类编号是从业务种类表中查得,例如

select case when business_code = '1' then income end as income_1, case when business_code = '2' then income end as income_2,.. . case when business_code = 'n' then income end as income_n  from other_business

我写sql.md语句时,传入业务种类编号 typelist,我的思路是

select@pageTag(){a.*,b.org_name,date_format(postdate,'%Y-%c-%d') as postdateformat, @trim(){ 

 @for(type in typelist){   

 case when business_code = #type# then cash end as 'income_'+#type#

 @}  

@} 

from other_business a left join orgs  b on a.org = b.org_id

我建立了OtherBusiness类

public class OtherBusiness extends TailBean {

但是实现运行时,无法从OtherBusiness类取得动态生成的列的值如 Income_1,income_2

请问我的需求,还有没有更好的解决方案

闲大赋 2018年02月12日

beetlsql 默认都是输出?,因此,你需要用text函数,告诉beetlsql原样输出,比如,

cash end as 'income_'+#text(type)#

这样就能达到你要的效果了

zhoyg 2018年02月12日

成功了,非常感谢大赋,后台正确输出sql语句

┏━━━━━ Debug [business.otherBusinessInfoList_page] ━━━┣ SQL: select a.*,b.org_name,date_format(postdate,'%Y-%c-%d') as postdateformat,'3' as test, case when business_code = '1' then income end as income_1, case when business_code = '2' then income end as income_2, case when business_code = '3' then income end as income_3, case when business_code = '4' then income end as income_4, case when business_code = '5' then income end as income_5, case when business_code = '6' then income end as income_6 from other_business a left join orgs b on a.org = b.org_id where 1=1 order by a.id limit ? , ?┣ 参数: [0, 15]┣ 位置: com.post.service.impl.BusinessServiceImpl.otherBusinessInfoList(BusinessServiceImpl.java:266)┣ 时间: 16ms┣ 结果: [12]┗━━━━━ Debug [business.otherBusinessInfoList_page] ━━━