try:
select sign,xb,100*count(*)/(select count(*) from from dj_table t1,cj_table t2
where t1.bh1=t2.bh1 and t1.bh2=t2.bh2
and km='语文') from (
select xb,decode(sign(cj-60),-1,'n','y') sign
from dj_table t1,cj_table t2
where t1.bh1=t2.bh1 and t1.bh2=t2.bh2
and km='语文'
) tb
group by xb,sign;
select sign,xb,100*count(*)/(select count(*) from from dj_table t1,cj_table t2
where t1.bh1=t2.bh1 and t1.bh2=t2.bh2
and km='语文') from (
select xb,decode(sign(cj-60),-1,'n','y') sign
from dj_table t1,cj_table t2
where t1.bh1=t2.bh1 and t1.bh2=t2.bh2
and km='语文'
) tb
group by xb,sign;
ORA-00903 无效表名
decode(sign(cj-60),-1,'n','y') 种的sign是干吗的呀,请指教:)
Purpose
If n<0, SIGN returns -1. If n=0, the function returns 0. If n>0, SIGN returns 1.
Example
SELECT SIGN(-15) "Sign" FROM DUAL;
Sign
----------
-1
select sign,xb,100*cnt/(select count(*) from dj_table t1,cj_table t2
where t1.bh1=t2.bh1 and t1.bh2=t2.bh2
and km='语文') from (
select sign,xb,count(*) cnt from (
select xb,decode(sign(cj-60),-1,'n','y') sign
from dj_table t1,cj_table t2
where t1.bh1=t2.bh1 and t1.bh2=t2.bh2
and km='语文'
) tb
group by xb,sign);
明天结贴,留给跟我同样问题的兄弟看看
sum(decode(xb,'女',jgl,0)) '及格率(女)'
from (
select sign,xb,100*cnt/(select count(*) from dj_table t1,cj_table t2
where t1.bh1=t2.bh1 and t1.bh2=t2.bh2
and km='语文') jgl from (
select sign,xb,count(*) cnt from (
select xb,decode(sign(cj-60),-1,'n','y') sign
from dj_table t1,cj_table t2
where t1.bh1=t2.bh1 and t1.bh2=t2.bh2
and km='语文'
) tb
group by xb,sign)
);
ORA-00923 未找到预期FROM关键字
我查了查decode和sign的用法,但是这两个函数组合出现在上面的SQL语句中是什么作用呢?
select sign,'男' xbn,sum(decode(xb,'男',jgl,0)) '及格率(男)','女' xbnv,
sum(decode(xb,'女',jgl,0)) '及格率(女)'
from (
select sign,xb,100*cnt/(select count(*) from dj_table t1,cj_table t2
where t1.bh1=t2.bh1 and t1.bh2=t2.bh2
and km='语文') jgl from (
select sign,xb,count(*) cnt from (
select xb,decode(sign(cj-60),-1,'n','y') sign
from dj_table t1,cj_table t2
where t1.bh1=t2.bh1 and t1.bh2=t2.bh2
and km='语文'
) tb
group by xb,sign) t
) tt;
bzszp(SongZip),你的语句中哪里是判断性别的?
sum(decode(sign(tt.cj-60),-1,0,1)*decode(tt.xb,'男',1,0))/sum(decode(tt.xb,'男',1,0)) 及格率(男),
sum(decode(sign(tt.cj-60),-1,0,1)*decode(tt.xb,'女',1,0))/sum(decode(tt.xb,'女',1,0)) 及格率(女)
from
(select a.xb,b.km,b.cj from dj_table a,cj_table b
where a.bh1=b.bh1 and a.bh2=b.bh2
and b.km='语文') tt
group by tt.km;
decode(sign(tt.cj-60),-1,0,1)判断是否及格,及格就记为1
decode(tt.xb,'男',1,0)判断男女,男记为1,女记为0那么相乘再取和表示男的及格个数。
分母表示男的总个数,则算出及格率。
from (
select sum(decode(xb,'男',decode(sign(cj-60),-1,0,1))) cm,
sum(decode(xb,'女',decode(sign(cj-60),-1,0,1))) cw
from dj_table t1,cj_table t2
where t1.bh1=t2.bh1 and t1.bh2=t2.bh2
and km='语文'
) tb,
(select count(*) zs
from dj_table t11,cj_table t22
where t11.bh1=t22.bh1 and t11.bh1=t22.bh2
and t22.km='语文') t3;
不过最先的回答已经可用了,结贴,先.