完整教程:
什么是 FreeMarker? - FreeMarker 中文官方参考手册
用法一
//先初始化要拼接的字符串头
<#assign sqlCol=" sum(if(classify_name = '塑料',sum_quantity,0)) as '塑料' ">
//定于需要拼接的字符串,上面哪个熟料也属于这个分类,如果后面,直接在数组后面加就行了
<#assign classify = ['废木板','废铜','木板','水泥','泡沫','炼钢','炼铁','玻璃','瓷砖','电池','纸皮','陶瓷'] >
<#list classify as cName>
//拼接select 后面的列查询,如果该分类为cName,那么这列就加值
<#assign sqlCol=sqlCol+", sum(if(classify_name = '${cName}',sum_quantity,0)) as '${cName}' ">
</#list>
//前端发来的条件
<#if monthAt??>
<#assign substrConditionMain="SUBSTR(main.create_time,9 , 2)">
<#assign substrConditionSlave="SUBSTR(create_time,9 , 2)">
<#assign timeSuffixStart=monthAt+"-01">
//freemarkter里面的语法,截取字符串5-6号位置字符,从0开始
<#assign currentMonth = monthAt[5..6]>
select
//强行填充一列自定义的数据进去
IFNULL(null,'${timeSuffixStart}') as 'create_time', -- string -- 日期
${sqlCol}
from iot.weight_statistic_ads
where
create_time = '${timeSuffixStart}' and project_code = "${projectCode}"
//产生一个循环,从2-get_days_in_month(monthAt[0..3]?number,monthAt[5..6]?number)
//get_days_in_month是一个函数
<#list 2..get_days_in_month(monthAt[0..3]?number,monthAt[5..6]?number) as num>
<#if num < 10 >
<#assign qTime = monthAt+'-0'+num>
<#else>
<#assign qTime = monthAt+'-'+num>
</#if>
UNION ALL
select
IFNULL(create_time,'${qTime}') as 'create_time',
${sqlCol}
from iot.weight_statistic_ads
where
create_time = '${qTime}' and project_code = "${projectCode}"
</#list>
</#if>
<#if yearAt??>
<#assign substrConditionMain="SUBSTR(main.create_time,1 , 7)">
<#assign substrConditionSlave="SUBSTR(create_time,1 , 7)">
<#assign timeSuffixStart=yearAt+"-01">
select
IFNULL(null,'${timeSuffixStart}') as 'create_time', -- string -- 日期
${sqlCol}
from iot.weight_statistic_ads
where
create_time >= '${timeSuffixStart+"-01"}' and create_time <= '${timeSuffixStart+"-31"}'
and project_code = "${projectCode}"
<#list 2..12 as num>
<#if num < 10 >
<#assign qTime = yearAt+'-0'+num >
<#else>
<#assign qTime = yearAt+'-'+num>
</#if>
UNION ALL
select
IFNULL(create_time,'${qTime}') as 'create_time',
${sqlCol}
from iot.weight_statistic_ads
where
create_time >= '${qTime+"-01"}' and create_time <= '${qTime+"-31"}'
and project_code = "${projectCode}"
</#list>
</#if>
//定义函数,函数名称 参数1,参数2
<#function get_days_in_month year month>
<#assign days = 0>
<#if month==2 >
<#if (year % 4)==0 >
<#assign days = 28>
<#else>
<#if (year % 100)==0>
<#assign days = 29>
<#else>
<#if (year % 400)==0>
<#assign days = 28>
<#else>
<#assign days = 29>
</#if>
</#if>
</#if>
<#else>
<#if (month == 4)||(month == 6)||(month == 9)||(month == 11) >
<#assign days = 30>
<#else>
<#assign days = 31>
</#if>
</#if>
<#return days >
</#function>