还有个,比如 A 合格 A 合格 B不合格 A 不合格 分组后想计算合格率 希望得到这样的结果 A 66.66% B 0%如何用SQL实现,我只是列举了关键字段,谁能实现我真的佩服膜拜这位大神
with t as( select 'A' yg,'合格' cj,'g' dfr from dual union all select 'B','合格','g' from dual union all select 'C','合格','g' from dual union all select 'D','不合格','g' from dual )select yg,sum(decode(cj,'不合格',1,0)) c from t group by yg YG C -- ---------- A 0 B 0 C 0 D 1
A,B,C,D是不可以写死的 这个是未知的 我只是举例 可能有很多个。
with t as( select 'A' id,'合格' cj from dual union all select 'A','合格' from dual union all select 'B','不合格' from dual union all select 'A','不合格' from dual )select id,round(sum(decode(cj,'合格',1,0))/count(1),4)*100||'%' from t group by id ID ROUND(SUM(DECODE(CJ,'合格',1,0 -- ----------------------------------------- A 66.67% B 0%
with t as( select 'A' id,'合格' cj from dual union all select 'A','合格' from dual union all select 'B','不合格' from dual union all select 'A','不合格' from dual ) select id, case when sum(decode(cj, '合格', 1, 0)) / count(1) >= 0.7 then 100 when sum(decode(cj, '合格', 1, 0)) / count(1) < 0.7 then GREATEST(100 - round((0.7 - sum(decode(cj, '合格', 1, 0)) / count(1)) / 0.001), 0) end from t group by id
ID CASEWHENSUM(DECODE(CJ,'合格',1 -- ------------------------------ A 67 B 0
create table t1 (col1 varchar(10));insert into t1 values ('66.2%'); insert into t1 values ('70.0%'); insert into t1 values ('63.3%'); insert into t1 values ('68.9%'); insert into t1 values ('65.1%'); insert into t1 values ('57.7%'); select col1,decode(sign(100-(700-substr(col1,1,length(col1)-1)*10)),1,100-(700-substr(col1,1,length(col1)-1)*10),0,0,-1,0) score from t1 col1 score --------------------------- 1 66.2% 62 2 70.0% 100 3 63.3% 33 4 68.9% 89 5 65.1% 51 6 57.7% 0
A 合格
A 合格
B不合格
A 不合格
分组后想计算合格率
希望得到这样的结果
A 66.66%
B 0%如何用SQL实现,我只是列举了关键字段,谁能实现我真的佩服膜拜这位大神
with t as(
select 'A' yg,'合格' cj,'g' dfr from dual
union all
select 'B','合格','g' from dual
union all
select 'C','合格','g' from dual
union all
select 'D','不合格','g' from dual
)select yg,sum(decode(cj,'不合格',1,0)) c from t group by yg
YG C
-- ----------
A 0
B 0
C 0
D 1
A,B,C,D是不可以写死的 这个是未知的 我只是举例 可能有很多个。
with t as(
select 'A' id,'合格' cj from dual
union all
select 'A','合格' from dual
union all
select 'B','不合格' from dual
union all
select 'A','不合格' from dual
)select id,round(sum(decode(cj,'合格',1,0))/count(1),4)*100||'%' from t group by id
ID ROUND(SUM(DECODE(CJ,'合格',1,0
-- -----------------------------------------
A 66.67%
B 0%
可以教下我吗 用decode 如果70% 就获得100分 每下降0.2个百分点就扣2分 扣完就是0分 如果超过70% 就是100分 谢谢高手
select 'A' id,'合格' cj from dual
union all
select 'A','合格' from dual
union all
select 'B','不合格' from dual
union all
select 'A','不合格' from dual
)
select id,
case
when sum(decode(cj, '合格', 1, 0)) / count(1) >= 0.7 then
100
when sum(decode(cj, '合格', 1, 0)) / count(1) < 0.7 then
GREATEST(100 - round((0.7 - sum(decode(cj, '合格', 1, 0)) / count(1)) /
0.001),
0)
end
from t
group by id
ID CASEWHENSUM(DECODE(CJ,'合格',1
-- ------------------------------
A 67
B 0
create table t1 (col1 varchar(10));insert into t1 values ('66.2%');
insert into t1 values ('70.0%');
insert into t1 values ('63.3%');
insert into t1 values ('68.9%');
insert into t1 values ('65.1%');
insert into t1 values ('57.7%');
select col1,decode(sign(100-(700-substr(col1,1,length(col1)-1)*10)),1,100-(700-substr(col1,1,length(col1)-1)*10),0,0,-1,0) score
from t1 col1 score
---------------------------
1 66.2% 62
2 70.0% 100
3 63.3% 33
4 68.9% 89
5 65.1% 51
6 57.7% 0
可以用case when 判断或者再嵌套一层