select t.err_grade, count(*) as num
from err_info t
group by t.err_grade ================================
返回: 1 10
2 37
4 20
5 16数据库中没有俄err_grade=3的纪录,所以没有=3的一行
但我想要这样的结果:
1 10
2 37
3 0 // 如果没有就返回0
4 20
5 16==================================急救
from err_info t
group by t.err_grade ================================
返回: 1 10
2 37
4 20
5 16数据库中没有俄err_grade=3的纪录,所以没有=3的一行
但我想要这样的结果:
1 10
2 37
3 0 // 如果没有就返回0
4 20
5 16==================================急救
假如最大值为100
select rid,sum(decode(err_grade,null,0,num)) as num
from (
select a.rid,t.err_grade,count(*) num
from (
select rownum rid
from dual
connect by level<=100 -----利用最大值建立辅助表
) a,err_info t
where a.rid=t.err_grade(+)
group by a.rid,t.err_grade
)
group by rid
g.err_grade,nvl(count(i.err_grade),0) as num
from
err_grade g
err_info i
where
g.err_grade=i.err_grade(+)
group by
g.err_grade Oracle 9i以上版本:select
g.err_grade,nvl(count(i.err_grade),0) as num
from
err_grade g
left outer join
err_info i
on
g.err_grade=i.err_grade
group by
g.err_grade
from err_info t
group by t.err_grade unionselect 3,decode(t1.err_grade,3,count(*),0)
from err_info t1
select t.err_grade, count(*) as num
from err_info t
group by t.err_grade unionselect 3,decode(t1.err_grade,3,count(*),0)
from err_info t1
group by t1.err_grade
取最大值:select max(t.err_grade) from err_info t;
假如最大值为100
select rid,sum(decode(err_grade,null,0,num)) as num
from (
select a.rid,t.err_grade,count(*) num
from (
select rownum rid
from dual
connect by level<=100 -----利用最大值建立辅助表
) a,err_info t
where a.rid=t.err_grade(+)
group by a.rid,t.err_grade
)
group by rid
关联到err_grade的那个表就可以了。SELECT a.err_grade, COUNT(b.*) AS num
FROM table_err_grade a, err_info b
WHERE a.err_grade = b.err_grade(+)
GROUP BY t.err_grade
FROM table_err_grade a, err_info b
WHERE a.err_grade = b.err_grade(+)
GROUP BY t.err_grade
select rid,count(err_grade) as num
from (
select rownum rid
from dual
connect by level<=100 -----利用最大值建立辅助表
) a,err_info t
where a.rid=t.err_grade(+)
group by a.rid
from (
select rownum rid
from dual
connect by level<=(select max(err_grade) from err_info)
) a,err_info t
where a.rid=t.err_grade(+)
group by a.rid
select rid,count(err_grade) as num
from (
select rownum rid
from dual
connect by level<=(select max(err_grade) from err_info)
) a,err_info t
where a.rid=t.err_grade(+)
group by a.rid