with t_all as(
SELECT dpclp.*
,sd2.dict_name as phase
,sd3.dict_name as active
,dio.name as designType
,dio.id as designTypeId
from dim_product_check_list_pool as dpclp
left join dim_inhouse_odm as dio on dio.id = dpclp.design_type_id
left join sys_dict as sd2 on sd2.dict_code=dpclp.phase_dict_code
left join sys_dict as sd3 on sd3.dict_code=dpclp.active_dict_code
)
,t_concat as(
SELECt
ta.id,ta.phase,ta.sort,ta.level1,ta.level2,ta.owner,ta.active,ta.update_by,ta.update_time
,array_to_string(ARRAY_AGG(ta.designTypeId), ',') as designTypeIds
,array_to_string(ARRAY_AGG(ta.designType),',') as designTypes
from t_all as ta
group by ta.id,ta.phase,ta.sort,ta.level1,ta.level2,ta.owner,ta.active,ta.update_by,ta.update_time
)
SELECT * from t_concat as tc
order by update_time desc
order by
${@org.apache.commons.lang.StringEscapeUtils@escapeSql(param.sortName)}
${@org.apache.commons.lang.StringEscapeUtils@escapeSql(param.sortBy)}
limit #{param.pageSize} offset #{param.offset}
SELECT count(1) from t_concat as tc
LOWER(concat(tc.designTypes,tc.phase,tc.sort,tc.level1,tc.level2,tc.owner,tc.active,tc.update_by))
like LOWER(concat('%',#{param.key},'%'))