oracle统计函数中数,Oracle统计函数

SELECT r.BADREASON_TYPE, count(r.BADREASON_TYPE),

"ROUND"(100*ratio_to_report(count(r.BADREASON_TYPE)) OVER (),2) pct_total

from "REPAIR" r WHERE r.BADREASON_TYPE is not NULL GROUP BY (r.BADREASON_TYPE) ORDER BY count(r.BADREASON_TYPE) DESC

结果：

请注意ratio_to_report用法

SELECT r.BADREASON_TYPE, count(r.BADREASON_TYPE),

ratio_to_report(count(r.BADREASON_TYPE)) OVER () pct_total

from "REPAIR" r WHERE r.BADREASON_TYPE is not NULL GROUP BY (r.BADREASON_TYPE) ORDER BY count(r.BADREASON_TYPE) DESC结果：

SELECT s.SEX,SUM(s."score") from STUDENT s GROUP BY rollup(s.SEX)

--分别统计男女生成绩总和及所有有总成绩

SELECT NVL(s.SEX, 3),SUM(s."score") from STUDENT s GROUP BY rollup(NVL(s.SEX, 3))

--统计男女生最高分

SELECT DISTINCT(s.SEX),

"MIN"(s."score") keep(dense_rank first ORDER BY s."score") over(partition by s.SEX),

"MAX"(s."score") keep(dense_rank last ORDER BY s."score") over(partition by s.SEX) FROM STUDENT s ;

-- 效果同上

SELECT DISTINCT(s.SEX),

"MIN"(s."score") keep(dense_rank first ORDER BY s."score") ,

"MAX"(s."score") keep(dense_rank last ORDER BY s."score")

FROM STUDENT s GROUP BY s.SEX;

-- 按成绩倒序，若为null,则置最后一条

SELECT * FROM STUDENT s ORDER BY s."score" DESC nulls last;

-- 按成绩正序，若为null,则置最后一条

SELECT * FROM STUDENT s ORDER BY s."score" nulls last;

SELECT s.GRADE,s.SEX  FROM STUDENT s GROUP BY (s.GRADE,s.SEX);

select sum(case when ssex = '女' then 1 else 0 end)"女生人数",sum(case when ssex = '男'then 1 else 0 end)"男生人数" from student;--纵向排列

--统计,没有合计

select r.USER_ID ,

sum(case WHEN r.BADREASON_TYPE='0' then 1 else 0 end) "作业",

sum(case when r.BADREASON_TYPE='1' then 1 else 0 end) "SMT" ,

sum(case when r.BADREASON_TYPE='2' then 1 else 0 end) "来料" ,

sum(case when r.BADREASON_TYPE='3' then 1 else 0 end) "其他"

from "REPAIR" r GROUP BY r.USER_ID;

-- 效果同上

SELECT r.USER_ID,

sum(decode(r.BADREASON_TYPE,'0',1,0))"作业",

sum(decode(r.BADREASON_TYPE,'1',1,0))"SMT",

sum(decode(r.BADREASON_TYPE,'2',1,0))"来料",

sum(decode(r.BADREASON_TYPE,'3',1,0))"其他"

from "REPAIR" r GROUP BY r.USER_ID;

-- select decode(grouping(name),1,'总分',name) 姓名,

-- sum(decode(km, '数学',cj,0)) 数学,

-- sum(decode(km, '英语',cj,0)) 英语,

-- sum(decode(km, '语文',cj,0)) 语文,

-- sum(cj) 总成绩

-- from test group by rollup(name)

--统计 纵合计:rollup会生产一条合计出来 ，

--grouping用法：可以接受一列，返回0或1。如果列值为空，则返回1，非空则返回0。

--此处表示：当grouping(r.USER_ID)=1时，维修人员为合计，当=0即不为空时，则维修人员为User_Id

SELECT decode(grouping(u.REAL_NAME),1,'合计',u.REAL_NAME) 维修人员ID,

sum(decode(r.BADREASON_TYPE,'0',1,0))"作业",

sum(decode(r.BADREASON_TYPE,'1',1,0))"SMT",

sum(decode(r.BADREASON_TYPE,'2',1,0))"来料",

sum(decode(r.BADREASON_TYPE,'3',1,0))"其他",

count(r.BADREASON_TYPE)"合计"

from "REPAIR" r , USER_INFO u WHERE r.USER_ID = u.USER_ID GROUP BY rollup(u.REAL_NAME);

--此统计方法和上面区别在于：nvl(express1,express2),express1 和 express2类型必须一致

SELECT nvl(r.USER_ID,123) 维修人员,

sum(decode(r.BADREASON_TYPE,'0',1,0))"作业",

sum(decode(r.BADREASON_TYPE,'1',1,0))"SMT",

sum(decode(r.BADREASON_TYPE,'2',1,0))"来料",

sum(decode(r.BADREASON_TYPE,'3',1,0))"其他",

count(r.BADREASON_TYPE)"合计"

from "REPAIR" r GROUP BY rollup(r.USER_ID);

SELECT count(r.BADREASON_TYPE),r.User_ID FROM "REPAIR" r GROUP BY r.USER_ID;

将两科都大于60的参考人员表中的rank更新为5，将只有一科大于60的参考人员表中的rank更新为3，将两科都小于60的参考人员表中的rank更新为0

UPDATE STU t SET t.rank = (case when t.id in (SELECT ss.id FROM SCORE ss WHERE ss.id in ( SELECT s.id  from SCORE s  GROUP BY  s.id HAVING count(s.subject) =2) AND ss.score > 60 GROUP BY ss.id HAVING count(ss.subject) =2) THEN 5 WHEN t.id in(SELECT ss.id FROM SCORE ss WHERE ss.id in ( SELECT s.id  from SCORE s  GROUP BY  s.id HAVING count(s.subject) =2) AND ss.score > 60 GROUP BY ss.id HAVING count(ss.subject) =1) THEN 3 WHEN t.id in(SELECT ss.id FROM SCORE ss WHERE ss.id in ( SELECT s.id  from SCORE s  GROUP BY  s.id HAVING count(s.subject) =2) AND ss.score > 60 GROUP BY ss.id HAVING count(ss.subject) =0) THEN 0 ELSE NULL END )