select sex as 性别, count(*) as 人数 from st group by rollup(sex);
select sex, sum(decode(sex, '1', 1, '2', 1)) from st
如果不行,,,说说我没有试。 please call me Rayn my QQ is 412552703
按性别分组 然后再rollup作个统计啊 sys@ORCL> create table st (sno number,sname varchar2(10),sex number);Table created.sys@ORCL> insert into st values(001,'aa',1);1 row created.sys@ORCL> insert into st values(002,'bb',1);1 row created.sys@ORCL> insert into st values(003,'cc',2);1 row created.sys@ORCL> insert into st values(004,'dd',1);1 row created.sys@ORCL> commit;Commit complete.sys@ORCL> select * from st; SNO SNAME SEX ---------- ---------- ---------- 1 aa 1 2 bb 1 3 cc 2 4 dd 1 sys@ORCL> select sex,count(sex) from st group by rollup(sex); SEX COUNT(SEX) ---------- ---------- 1 3 2 1 4
group by rollup() 对每组数据进行合计
学习了,之前只用group by,原来还有group by rollup。
这是对group by的扩展 cube也行 rollup也行
谢谢 还有一个问题就是我怎么让显示的一变成男性,2变成女性 性别 人数 男 m 女 n m+n
select decode(sex,1,'男','2','女') 性别, count(*) as 人数 from st group by rollup(sex);
case..when 或 decode都行 比如:decode(sex,1,"男",2,"女")
select case sex when '1' then '男' when '2' then '女' end AS 性别,COUNT(*) as 人数 from st GROUP BY ROLLUP(SEX)
group by rollup(columnList)会按照the first n,n-1,n-2.....0共n+1中情况分组,进行小计求和.decode(column,v1,v11 ,v2,v22 ,v3 v33 ,default) 或者case [expression] when [expression] then[expression] when [expression] then[expression] when [expression] then[expression] else default end;都有 if else的功能。
please call me Rayn
my QQ is 412552703
然后再rollup作个统计啊
sys@ORCL> create table st (sno number,sname varchar2(10),sex number);Table created.sys@ORCL> insert into st values(001,'aa',1);1 row created.sys@ORCL> insert into st values(002,'bb',1);1 row created.sys@ORCL> insert into st values(003,'cc',2);1 row created.sys@ORCL> insert into st values(004,'dd',1);1 row created.sys@ORCL> commit;Commit complete.sys@ORCL> select * from st; SNO SNAME SEX
---------- ---------- ----------
1 aa 1
2 bb 1
3 cc 2
4 dd 1
sys@ORCL> select sex,count(sex) from st group by rollup(sex); SEX COUNT(SEX)
---------- ----------
1 3
2 1
4
cube也行
rollup也行
性别 人数
男 m
女 n
m+n
或
decode都行
比如:decode(sex,1,"男",2,"女")
when '1' then '男'
when '2' then '女'
end
AS 性别,COUNT(*) as 人数
from st
GROUP BY ROLLUP(SEX)
,v2,v22
,v3 v33
,default)
或者case [expression] when [expression] then[expression]
when [expression] then[expression]
when [expression] then[expression]
else default
end;都有 if else的功能。