create table tttt(name varchar2(20),status int);insert into tttt values('小王',0); insert into tttt values('小王',1); insert into tttt values('小王',0); insert into tttt values('小赵',1); insert into tttt values('小赵',1);select * from tttt select name,sum(failure) 失败,sum(success) 成功 from( select name,decode(status,0,1,0) failure,decode(status,1,1,0) success from tttt) group by name
select t.name, nvl(count(decode(t.score,0,0,null)),0), nvl(count(decode(t.score,1,1,null)),0) from temp t group by t.name
select name , sum( decode(status,0,1,0) ) as 成功, sum( decode(status,1,1,0) ) as 失败 from a group by name;
假如你的表名是 ab ,name 是姓名 score 是标志 select name 姓名, sum(case score when '1' then 1 else 0 end ) 成功, sum(case score when '0' then 1 else 0 end) 失败 from ab group by name
对了,你的SCORE是什么字段,我上面写的是你是VARCHAR2的
select t.name, nvl(count(decode(t.score,0,0,null)),0) as wins, nvl(count(decode(t.score,1,1,null)),0) as lose from temp t group by t.name 引申一个问题:如下表 NAME WIN LOSE a 1 1 a 1 2 b 0 2 b 3 4 得到 NAME WINS LOSES b 3 6 a 2 3select distinct(name),wins,loses from (select name,sum(win) over (partition by name) wins,sum(lose) over (partition by name) loses from temp)
insert into tttt values('小王',1);
insert into tttt values('小王',0);
insert into tttt values('小赵',1);
insert into tttt values('小赵',1);select * from tttt
select name,sum(failure) 失败,sum(success) 成功
from(
select name,decode(status,0,1,0) failure,decode(status,1,1,0) success
from tttt)
group by name
nvl(count(decode(t.score,0,0,null)),0),
nvl(count(decode(t.score,1,1,null)),0)
from temp t
group by t.name
select name , sum( decode(status,0,1,0) ) as 成功, sum( decode(status,1,1,0) ) as 失败 from a group by name;
select name 姓名,
sum(case score when '1' then 1 else 0 end ) 成功,
sum(case score when '0' then 1 else 0 end) 失败
from ab
group by name
nvl(count(decode(t.score,0,0,null)),0) as wins,
nvl(count(decode(t.score,1,1,null)),0) as lose
from temp t
group by t.name 引申一个问题:如下表
NAME WIN LOSE
a 1 1
a 1 2
b 0 2
b 3 4
得到
NAME WINS LOSES
b 3 6
a 2 3select distinct(name),wins,loses from
(select name,sum(win) over (partition by name) wins,sum(lose) over (partition by name) loses from temp)