同一表中根据不同条件,查出领取金额、消费金额,然后进行相减,得到月底冲零金额
计算公式为:领取金额 - 消费金额 = 月底冲零金额
--领取金额(查出结果为:77800)
select sum(t.subsidyoddfare) from rec_cust_acc t where t.acccode = '104' and t.opdt between
to_date(concat('2010-10-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-10-31',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss');
--消费金额(查出结果为:76487.2)
select sum(t.subopfare) from rec_cust_acc t where t.acccode = '210' and t.opdt between
to_date(concat('2010-10-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-10-31',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss');
怎样让这两个结果进行相减,得到月底冲零金额?
计算公式为:领取金额 - 消费金额 = 月底冲零金额
--领取金额(查出结果为:77800)
select sum(t.subsidyoddfare) from rec_cust_acc t where t.acccode = '104' and t.opdt between
to_date(concat('2010-10-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-10-31',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss');
--消费金额(查出结果为:76487.2)
select sum(t.subopfare) from rec_cust_acc t where t.acccode = '210' and t.opdt between
to_date(concat('2010-10-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-10-31',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss');
怎样让这两个结果进行相减,得到月底冲零金额?
declare @b decimal(12,2)
select @a = sum(t.subsidyoddfare) from rec_cust_acc t where t.acccode = '104' and t.opdt between
to_date(concat('2010-10-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-10-31',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss');
select @b = sum(t.subsidyoddfare) from rec_cust_acc t where t.acccode = '104' and t.opdt between
to_date(concat('2010-10-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-10-31',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss');
select @a-@b
--如果acccode为104取它的subsidyoddfare,如果acccode为210取-subsidyoddfare,其他的为0
select sum(decode(acccode,'104',t.subsidyoddfare,'210',-t.subsidyoddfare,0))
from rec_cust_acc t
where t.opdt between
to_date(concat('2010-10-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-10-31',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss');
把它当成sql语句执行报这样的错误:
select sum(decode(acccode,'104',t.subsidyoddfare,'210',-t.subopfare,0))
from rec_cust_acc t
where t.opdt between
to_date(concat('2010-10-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-10-31',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss');
select sum(decode(t.acccode,'104',t.subsidyoddfare,0)) subsidyoddfare,
sum(decode(t.acccode,'210',t.subopfare,0)) subopfare,
sum(decode(t.acccode,'104',t.subsidyoddfare,0))-sum(decode(t.acccode,'210',t.subopfare,0)),
sum(decode(t.acccode,'104',t.subsidyoddfare,'210',-t.subopfare))
from rec_cust_acc t where t.opdt between
to_date(concat('2010-10-01',' 00:00:00'),'yyyy-mm-dd hh24:mi:ss') and
to_date(concat('2010-10-31',' 23:59:59'),'yyyy-mm-dd hh24:mi:ss');
select (sql) - (sql) from dual;