id_, main_id_, fill_date_, enterprise_code_, sop_, customer_, brand_, model_, actual_, budget_, actual_ytd_, budget_ytd_, remarks, last_time_ SELECT m.fill_year_ as year,sum(d.actual_ytd_) as actual,sum(d.budget_ytd_) as budget FROM qf_operation_sop_d d LEFT JOIN qf_operation_sop_m m ON m.id_ = d.main_id_ LEFT JOIN ( SELECT m.fill_year_ AS year, max(m.fill_month_) AS month FROM qf_operation_sop_d d LEFT JOIN qf_operation_sop_m m ON m.id_ = d.main_id_ GROUP BY m.fill_year_ ) q on q.year = m.fill_year_ and q.month = m.fill_month_ and m.fill_year_ >= #{vo.startYear} AND #{vo.endYear} >= m.fill_year_ AND #{vo.year} = m.fill_year_ GROUP BY m.fill_year_ ORDER BY m.fill_year_ SELECT d.customer_ as customer,sum(d.actual_ytd_) as actual,sum(d.budget_ytd_) as budget FROM qf_operation_sop_d d LEFT JOIN qf_operation_sop_m m ON m.id_ = d.main_id_ LEFT JOIN ( SELECT d.customer_, max(m.fill_month_) AS month FROM qf_operation_sop_d d LEFT JOIN qf_operation_sop_m m ON m.id_ = d.main_id_ GROUP BY d.customer_ ) q on q.month = m.fill_month_ AND #{vo.year} = m.fill_year_ AND d.customer_ = #{vo.customer} GROUP BY d.customer_ ORDER BY d.customer_ SELECT m.fill_month_ as month, sum(d.actual_) as actual, sum(d.budget_) as budget FROM qf_operation_sop_d d LEFT JOIN qf_operation_sop_m m ON m.id_ = d.main_id_ and m.fill_year_ = #{vo.year} and m.fill_month_ >= #{vo.startMonth} AND #{vo.endMonth} >= m.fill_month_ AND d.customer_ = #{vo.customer} GROUP BY m.fill_month_ ORDER BY m.fill_month_ ASC