Oracle⾼级查询之分组统计⼀、分组统计
--1.每位同学的总成绩(空值不参与统计)
lect s_id,sum(score) a
from t_score
group by s_id
重点:分析下句为什么错(报什么错),根据出错提⽰信息可以如何修改
2.
lect s_id,s_name,sum(score) a
from t_score
group by s_id
错误:ORA-00979:NOT a GROUP BY expression
蜃什么意思-
-错误原因:既不是分组字段,⼜不是统计字段。
思路:s_name要么分组,要么统计。
--下⾯报什么错
lect s_id,s_name,sum(score) a
from t_score
group by s_id,s_name
错误原因:s_id,s_name之间的,为中⽂状态。
--在Oracle以及SQL Server中或者说⼏乎所⽤的编程语⾔⾥符号都是英⽂状态下的.
北京小众景点--使⽤统计+分组.查询每位同学的总成绩
lect s_id,COUNT(s_name),sum(score) a
一组词from t_score
group by s_id
--max()函数参数是否可以问字符呢?答案是肯定的.
lect s_id,MAX(s_name),sum(score) a
from t_score
group by s_id
--字符最⼤值:按字典顺序,后⾯的值⼤。(实质他是按ascii排序的)
lect MAX(s_name) a from t_score小贱资料
--上⾯的过程请推理出分组统计的语法
SELECT 分组字段,统计字段
FROM 表
GROUP BY 分组字段
衣着打扮HAVING 统计字段表达式大对虾的做法
3.下⾯两句功能有什么差别:
雷雨天
--按每位学员分组,然后统计每位学⽣的总成绩
打电话教案lect s_id,sum(score) a from t_score group by s_id;
--先按科⽬分组,然后统计每个科⽬的总成绩
lect sub_name,sum(score) a from t_score group by sub_name;
看看下⾯报什么错?
lect s_id,sum(score) a from t_score group by sub_name
答案:与刚才的⼀样s_id 没有参与统计或者分组
SELECT * FROM t_score;
4.先分组,后统计,⽤having
lect s_id,s_name,sum(score) a
from t_score
group by s_id,s_name
5.推理下⾯查询结果,以及sql执⾏逻辑上的步骤
lect s_id,s_name,sum(score) x
from t_score
where score>60
group by s_id,s_name
having sum(score)>80
order by x desc
思考:where与having有什么异同点?
-
-结论:where是在分组前进⾏过滤,⽽having是在分组后进⾏过滤(过滤的分组信息) 6.别名问题
lect s_id,s_name,sum(score) a
from t_score
group by s_id,s_name
having sum(score)>60 -- 不能 having a >60
order by a
原因:为什么不能 having a >60?
--个⼈理解:应该是sum(score)还没有统计出来。因为having还要过滤。
希望朋友多多指出不对的地⽅,如果有什么可以帮助的或者疑问加QQ:1024102954
如果有朋友想参加JAVA培训或者想咨询这⽅⾯的东西也可以联系我。很乐意为⼤家服务!