【MySQL】常用操作

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