group by
--
使用
group by
语句查询
select count(studName) from student_1 group by study
select count(uname) from userluo group by age;
根据某一个字段进行分组
select age,count(*) from userluo group by age;
select uname,count(*) from userluo group by uname;
矛盾。。。。。
尽管
deptno
和
deptname
是一一对应的,但
group by
不知道。。。按照语法来检查。。。
Group by
后面也可以有多个字段,但是要求和前面一致。。。。。。。。
--
单列分组
--
显示每个部门的平均工资和最高工资
Select deptno,avg(sal),max(sal) from emp
group by deptno;
--
多列分组
--
显示每个部门、每种岗位的平均工资和最高工资
Select deptno,job,avg(sal),max(sal) from emp
group by deptno,job; --
注意:
deptno
和
job
完全相同才显示一条记录。
Having
和
group by
配套使用。。。
可以在
group
分组以后再过滤,
select age,count(*) from userluo group by age having count(*)<2;
--
使用
having
语句
select trim(studName),count(*) from student_1 group by trim(studName) having count(*)=2;
select count(*) studName from student_1 group by studName having count(*)=2;
order by
--
使用
order by
语句
,desc
是降序排列,
asc
是升序排列
,
默认为升序排列
select * from student_1 order by age desc;
select * from student_1 order by age sname desc;
先按
age
排列,相同的
age
再按
sname
排列,