1、拼接两个字段,并将其中一个字段的多余的0去掉
select
<include refid="Base_Column_List" />
,
<include refid="Blob_Column_List" />,
concat(trim(TRAILING 0 from cast(maturity_term_day as char)),'',maturity_term_unit) as termStr
from bond_base
当某个字段为某个值时,case then
SELECT
t.trader_id as clientId,t.trader_name as clientName,t.trader_name as clientShortName,
c.code as clientType,c.name as clientTypeName,d.DEPARTMENT_NAME as team,
t.trader_name_pinyin as clientPinyin,
(case t.trader_email when '[]' then '' else t.trader_email end) as email,
(case t.trader_qq when '[]' then '' else t.trader_qq end) as QQ,
(case t.trader_tel when '[]' then '' else t.trader_tel end) as mobile
from ubm_trader_info t
left join ubm_code_info c on c.code = 'Trader'
left join ubm_dept_info d on d.DEPARTMENT_ID = t.DEPARTMENT_ID
SELECT b.broker_id as clientId,b.name as clientName,b.name as clientShortName,
c.code as clientType,c.name as clientTypeName,t.TEAM_NAME as team,b.login_email as email,
b.broker_name_pinyin as clientPinyin,
REPLACE(REPLACE(b.qq, '[', ''), ']', '') as QQ,CONCAT_WS(',', NULLIF(b.telephone, ''), NULLIF(b.phone, '')) AS mobile
from ubm_broker_info b
left join ubm_code_info c on c.code = 'Broker'
left join ubm_team_info t on t.TEAM_ID = b.team_id