select t.sex,sum(num) num from (select sex,1 num from tmp2 union all select '女',0 num from dual union all select '男',0 num from dual ) t group by t.sex
--如: Select x.sex,count(t.id) as cn from 表 t right Join (Select '男' as sex from dual union Select '女' as sex from dual ) x on t.sex=x.sex group by x.sex
Select b.sex,count(t.id) from 表 a, (Select '男' as sex from dual union Select '女' as sex from dual ) b where b.sex=a.sex(+) group by b.sex
SQL> select sex.name,count(t1.sex) 2 from (select 1 as id,'nan' as sex from dual 3 union all 4 select 2 as id,'nan' as sex from dual 5 union all 6 select 3 as id,'nan' as sex from dual 7 )t1, 8 ( 9 select 1 as id,'nan' as name from dual 10 union all 11 select 2 as id,'nv' as name from dual 12 )sex 13 where sex.name = t1.sex(+) 14 group by sex.name;NAME COUNT(T1.SEX) ---- ------------- nan 3 nv 0
select t.sex,count(t.id) num from (select t2.sex,t1.id from tmp2 t1, ( select '女' sex from dual union all select '男' sex from dual ) t2 where t1.sex(+)=t2.sex) t group by t.sex
Select t.sex,sum(num) num From (Select sex,1 num From @TB)t Group by t.sex
(select sex,1 num from tmp2
union all
select '女',0 num from dual
union all
select '男',0 num from dual
) t
group by t.sex
Select x.sex,count(t.id) as cn from 表 t
right Join (Select '男' as sex from dual
union
Select '女' as sex from dual ) x
on t.sex=x.sex group by x.sex
(Select '男' as sex from dual
union
Select '女' as sex from dual ) b
where b.sex=a.sex(+)
group by b.sex
2 from (select 1 as id,'nan' as sex from dual
3 union all
4 select 2 as id,'nan' as sex from dual
5 union all
6 select 3 as id,'nan' as sex from dual
7 )t1,
8 (
9 select 1 as id,'nan' as name from dual
10 union all
11 select 2 as id,'nv' as name from dual
12 )sex
13 where sex.name = t1.sex(+)
14 group by sex.name;NAME COUNT(T1.SEX)
---- -------------
nan 3
nv 0
(select t2.sex,t1.id from tmp2 t1,
(
select '女' sex from dual
union all
select '男' sex from dual
) t2 where t1.sex(+)=t2.sex) t
group by t.sex
(Select sex,1 num From @TB)t
Group by t.sex