select s.kjkm,
sum(case WHEN state = 2and pay_time >=#{start_time}and pay_time <=#{end_time}THEN paid_amount ELSE0END)month_paid,
sum(case WHEN state =2AND pay_time >=#{start_time}and pay_time <=#{end_time}and end_time >=#{end_time}THEN paid_amount ELSE0END)month_receivable,
sum(case WHEN state =2AND state =2and end_time >=#{end_time}THEN paid_amount ELSE0END)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 = 2and
t1.pay_time >=#{start_time}and t1.end_time <=#{end_time}and t1.end_time >=#{start_time}groupby t1.kjkm)s_b right JOIN
(select sum(t1.paid_amount)as paid_apport ,t1.kjkm from crm_wyfee_settle t1 WHERE t1.state = 2and
t1.end_time >=#{start_time}and t1.pay_time <=#{end_time}and t1.pay_time >=#{start_time}groupby 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 = 2and
His.pay_time >=#{start_time}and
His.pay_time <=#{end_time}and His.end_time <#{start_time}GROUPBY His.kjkm)t1 RIGHT JOIN
(SELECT SUM(His.paid_amount) as paid_amount ,His.kjkm from crm_wyfee_settle His WHERE His.state = 2AND
His.end_time <#{start_time}GROUPBY His.kjkm)t2 on t1.kjkm = t2.kjkm)t3 WHERE t3.kjkm =s.kjkm),0)history_rate
from crm_wyfee_settle s groupby s.kjkm;
最近在做财务报表这一项的功能,主要设计的SQL如下select s.kjkm, sum(case WHEN state = 2 and pay_time &amp;gt;=#{start_time} and pay_time &amp;lt;=#{end_time} THEN paid_amount ELSE 0 END)month_paid, sum(case WH...