querysql
.append("select o.orgid,date_format(o.orderdate,'%Y'),count(*),sum(case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)" +
"as totalmoney,sum(case when o.payamount is not NULL then o.payamount else '0.00' end) as paymoney," +
" (sum(case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)-sum(case when o.payamount is not NULL and (case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)!='0.00' then o.payamount else '0.00' end)) " +
"as nopaymoney from htglview o where 1=1 and o.contractState in (5,8,9,10,11) ");
.append("select o.orgid,date_format(o.orderdate,'%Y'),count(*),sum(case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)" +
"as totalmoney,sum(case when o.payamount is not NULL then o.payamount else '0.00' end) as paymoney," +
" (sum(case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)-sum(case when o.payamount is not NULL and (case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)!='0.00' then o.payamount else '0.00' end)) " +
"as nopaymoney from htglview o where 1=1 and o.contractState in (5,8,9,10,11) ");
.append("select o.orgid,date_format(o.orderdate,'%Y'),count(*),sum(case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)" +
"as totalmoney,sum(case when o.payamount is not NULL then o.payamount else '0.00' end) as paymoney," +
" (sum(case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)-sum(case when o.payamount is not NULL and (case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)!='0.00' then o.payamount else '0.00' end)) " +
"as nopaymoney from htglview o where 1=1 and o.contractState in (5,8,9,10,11) ");
判断 o.finalamount!='0.00' 成立 则累加 o.finalamount 字段 否则 累加o.contractamount
其他的类似
date_format(o.orderdate,'%Y'), 输出 orderdate 年的数字
count(*) 统计结果集
sum(case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)" +
"as totalmoney 已经解释过累加规则了 以别名 totalmoney 输出结果
sum(case when o.payamount is not NULL then o.payamount else '0.00' end) as paymoney同理
sum(case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)-sum(case when o.payamount is not NULL and (case when o.finalamount!='0.00' then o.finalamount else o.contractamount end)!='0.00' then o.payamount else '0.00' end)) " +
"as nopaymoney
nopaymoney 是把 里面2个SUM结果集想减 case when then 这个就不解释 同理
from htglview o where 1=1 and o.contractState in (5,8,9,10,11) 这个我就不用解释了吧
复制到PLSQL里,格式化的功能。