＋设为首页 ＋添加到收藏夹
我有一个表(t_bm),聪明的人会猜到时部门表或者报名表,猜对了,是报名表,里面包括这么两个字段:bm_xb,bm_mz,代表性别和民族,我想要的sql语句是这样:一句sql(为了简单节省资源)求出总人数,性别是男的的人数,民族为汉族的人数.从前用sqlserver是这样写的:
select count(*) as zrs ,sum(if(bm_xb =男,1,0)) as nrs,sum(if(bm_mz = 汉族,1,0)) as hrs from t_bm ;但是到了orcale里面不会写了,虚心求教高人,如何实现!是否能一句(检索一遍数据库)实现,如果不能那么应该怎么做?刚用orcal,还不会写存储过程!
select count(1) zrs,
sum(decode(bm_xb,男,1,0)) nrs,
sum(decode(bm_mz,汉,1,0)) hrs
from t_bm;
select count(*) as zrs,sum(decode(bm_xb,男,1,0)) as nrs,sum(decode(bm_mz,汉,1,0)) as hrs from t_bm;这就是oracle里对应于你sqlserver里的写法，而且对表t_bm只扫描了一次！
select count(*) as zrs,
sum(decode(bm_xb,男,1,0)) as nrs,
sum(decode(bm_mz,汉族,1,0)) as hrs
from t_bm
楼主散分。
就是像sbaz(万神渡劫) 这种做法！
select count(*) total,
sum(decode(bm_xb,男,1,0)) male,
sum(decode(bm_mz,汉,1,0)) han_nationality
from t_bm;
select count(*) as zrs,
sum(decode(bm_xb,男,1,0)) as nrs,
sum(decode(bm_mz,汉族,1,0)) as hrs
from t_bm
的方法比嵌套的好多了。
select count(*) as zrs,
sum(decode(bm_xb,男,1,0)) as nrs,
sum(decode(bm_mz,汉族,1,0)) as hrs
from t_bm
正确，楼主散分呀。
select count(ROWID) as zrs, -------效率会高一点
sum(decode(bm_xb,男,1,0)) as nrs,
sum(decode(bm_mz,汉族,1,0)) as hrs
from t_bm
where rownum=1 -------执行效率可能会好一点(如果需求的返行是1行的情况下)
select count(*) as zrs,
sum(decode(bm_xb,男,1,0)) as nrs,
sum(decode(bm_mz,汉族,1,0)) as hrs
from t_bm
正确
