当前位置: 代码迷 >> Oracle开发 >> 关于group by的有关问题
  详细解决方案

关于group by的有关问题

热度:26   发布时间:2016-04-24 07:47:35.0
关于group by的问题
有表如下
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
  相关解决方案