需求：按月份统计查询合同金额，回款金额

按月份统计查询合同金额，回款金额,按时间倒序排列

注：合同表：base_contract
回款表：base_actual

    select DATE_FORMAT(PERSONNEL_DATE,'%Y-%m') as diffDate,
    ifnull(sum(case when 1=1  then amount else 0 end),0) as amount,0 as hkamount
    from base_contract 
    group by DATE_FORMAT(PERSONNEL_DATE,'%Y-%m')

    union all 

    select DATE_FORMAT(receive_date,'%Y-%m') receiveDate,
    0,ifnull(sum(case when 1=1 then amount else 0 end),0) as hkamount
    from base_actual 
    group by DATE_FORMAT(receive_date,'%Y-%m')

    order by diffDate desc

比较讨论：

    select a.diffDate,MAX(a.amount) as amount, MAX(a.hkamount) as hkamount 
    from 
    (select DATE_FORMAT(PERSONNEL_DATE,'%Y-%m') as diffDate,
    ifnull(sum(case when 1=1  then amount else 0 end),0) as amount,0 as hkamount
    from base_contract 
    group by DATE_FORMAT(PERSONNEL_DATE,'%Y-%m')

    union all 

    select DATE_FORMAT(receive_date,'%Y-%m') receiveDate,
    0,ifnull(sum(case when 1=1 then amount else 0 end),0) as hkamount
    from base_actual 
    group by DATE_FORMAT(receive_date,'%Y-%m')
    )a
    GROUP BY a.diffDate
    order by diffDate desc