关于财务报表的SQL

• 最近在做财务报表这一项的功能，主要设计的SQL如下

select s.kjkm,
    sum(case WHEN state = 2 and pay_time  >=#{start_time} and pay_time <=#{end_time} THEN paid_amount ELSE 0 END)month_paid,
    sum(case WHEN state =2 AND pay_time  >=#{start_time} and pay_time <=#{end_time} and end_time  >=#{end_time} THEN paid_amount ELSE 0 END)month_receivable,
    sum(case WHEN state =2 AND state =2 and end_time >=#{end_time} THEN paid_amount ELSE 0 END)month_stop_receivable,

    ISNULL((SELECT ISNULL(s_b.paid_apportionment , 0) + s_a.paid_apport  FROM 
    (select sum(t1.paid_amount)as paid_apportionment  ,t1.kjkm from crm_wyfee_settle t1 WHERE t1.state = 2 and 
    t1.pay_time  >=#{start_time} and t1.end_time <=#{end_time} and t1.end_time  >=#{start_time} group by t1.kjkm)s_b right JOIN
    (select sum(t1.paid_amount)as paid_apport  ,t1.kjkm from crm_wyfee_settle t1 WHERE t1.state = 2 and 
    t1.end_time >=#{start_time} and t1.pay_time <=#{end_time} and t1.pay_time >=#{start_time} group by t1.kjkm)s_a
    on s_a.kjkm = s_b.kjkm WHERE s.kjkm =s_a.kjkm),0)month_share,

    ISNULL((SELECT t3.pay_amount/t3.paid_amount from 
    (SELECT ISNULL(t1.kjkm,t2.kjkm)kjkm,ISNULL(t1.pay_amount,0)pay_amount,t2.paid_amount from 
    (SELECT sum(His.paid_amount) as pay_amount ,His.kjkm from crm_wyfee_settle His WHERE His.state = 2 and 
    His.pay_time  >=#{start_time} and 
    His.pay_time <=#{end_time} and His.end_time <#{start_time} GROUP BY His.kjkm)t1 RIGHT JOIN
    (SELECT SUM(His.paid_amount) as paid_amount ,His.kjkm from crm_wyfee_settle His WHERE His.state = 2 AND
    His.end_time <#{start_time} GROUP BY His.kjkm)t2 on t1.kjkm = t2.kjkm)t3 WHERE t3.kjkm =s.kjkm),0)history_rate

    from crm_wyfee_settle s group by s.kjkm;

• 以上SQL主要涉及到函数sum，isnull以及分组group by的使用