加个group by select deptno,count(comm) from emp group by deptno
写错了,我原来的sql语句就是: select name,count(n) num from table1 group by name
你表里面如果没有"乙"这条数据的话 怎么也不可能显示出来啊你说的null是指哪个字段空?
难道是你NAME里本来就没有"乙 "? select * from ( select name,count(n) num from table1 group by name union select "乙",0 from dual )
不好意思,我把问题说简单了。 我现在统计是按条件进行统计, 比如:select name,count(n) num from table1 where time1<某时间 group by name 这样符合条件的name字段没有乙,所以结果不包括乙 name num 甲 3 丙 1 丁 5 但是呢,我想将乙也列出,如下所示 甲 3 乙 0 丙 1 丁 5这样的话SQL语句怎么写?
那就用视图代替表 select t1.name,count(t2.n) from (select distinct name from TABNAME)t1, TABNAME t2 where t1.name=t2.name(+) and t2.COLNAME(+)=.... and t2.COLNAME1(+)....(过滤条件,注意这个加号) group by t1.name
--选出过滤条件满足的 select deptno,count(comm) comm from emp group by deptno having count(comm)>0 union all --选出过滤条件不满足的 select distinct deptno ,0 as comm from emp where deptno not in (select deptno from emp group by deptno having count(comm)>0)
select deptno,count(comm)
from emp
group by deptno
select name,count(n) num from table1 group by name
怎么也不可能显示出来啊你说的null是指哪个字段空?
select * from (
select name,count(n) num from table1 group by name
union
select "乙",0 from dual
)
我现在统计是按条件进行统计,
比如:select name,count(n) num from table1 where time1<某时间 group by name
这样符合条件的name字段没有乙,所以结果不包括乙
name num
甲 3
丙 1
丁 5 但是呢,我想将乙也列出,如下所示
甲 3
乙 0
丙 1
丁 5这样的话SQL语句怎么写?
可以用两个表做个关联如果你只想加少量用户上去
那么使用6#的方法使用UNION也可以
union不好
select t1.name,count(t2.n)
from
(select distinct name from TABNAME)t1,
TABNAME t2
where t1.name=t2.name(+)
and t2.COLNAME(+)=....
and t2.COLNAME1(+)....(过滤条件,注意这个加号)
group by t1.name
--选出过滤条件满足的
select deptno,count(comm) comm
from emp
group by deptno having count(comm)>0
union all
--选出过滤条件不满足的
select distinct deptno ,0 as comm from emp
where deptno not in
(select deptno
from emp
group by deptno having count(comm)>0)