SQL> select * from test;NAME STATE COST --------- ---------- ---------- jiang 0 40 jiang 1 200 bbbbb 1 113 bbbbb 0 11已用时间: 00: 00: 00.01 SQL> select name,sum(case 2 when state = 0 then -cost 3 when state = 1 then cost 4 end ) cost 5 from test 6 group by name 7 /NAME COST --------- ---------- bbbbb 102 jiang 160已用时间: 00: 00: 00.00
select 姓名,SUM(DECODE(状态,'充值',额度,'消费',-1*额度)) from A group by 姓名
create table qxjtest1 ( name varchar2(20), state varchar2(20), forelock number(20,1) );insert into qxjtest1 values ('吴','消费 ','40.0'); insert into qxjtest1 values ('吴','充值 ','133.0'); insert into qxjtest1 values ('刘','充值 ','200.0'); insert into qxjtest1 values ('刘','消费 ','11.0');select * from qxjtest1;select name,sum(case when trim(state)='消费' then forelock*-1 else forelock end ) as cost from qxjtest1 group by name;
--------- ---------- ----------
jiang 0 40
jiang 1 200
bbbbb 1 113
bbbbb 0 11已用时间: 00: 00: 00.01
SQL> select name,sum(case
2 when state = 0 then -cost
3 when state = 1 then cost
4 end ) cost
5 from test
6 group by name
7 /NAME COST
--------- ----------
bbbbb 102
jiang 160已用时间: 00: 00: 00.00
我的方法就补角死板!
就不说了!不过一样可以!楼主给分吧!
为什么duanzilin(寻) 的方法最后一行会多出一个0来?
(
name varchar2(20),
state varchar2(20),
forelock number(20,1)
);insert into qxjtest1 values ('吴','消费 ','40.0');
insert into qxjtest1 values ('吴','充值 ','133.0');
insert into qxjtest1 values ('刘','充值 ','200.0');
insert into qxjtest1 values ('刘','消费 ','11.0');select * from qxjtest1;select name,sum(case when trim(state)='消费'
then forelock*-1
else forelock end ) as cost from qxjtest1 group by name;
至于我的为什么最后一行会多出一个0,不是很明白。
from qxjtest1 group by name