有表如下
id sex
1 男
2 男
3 男
按sex分组查询，想求得结果：
男 3
女 0
不知如何SQL如何写
------解决方案--------------------
--如：
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