# ORDER

# 排序介绍

mysql对查询结果使用order by 进行排序

  • 对任何字段进行排序
  • desc降序 asc升序
  • 对别名字段可排序
  • 对函数结果可排序
  • 支持多列表排序
  • 排序受校对规则影响(请查看其他章节课程了解校对规则)=

# 排序实例

从男到女排序

SELECT * FROM stu ORDER BY sex ASC;

从男到女排序年龄从小到大排序

SELECT * FROM stu ORDER BY sex ASC,birthday DESC;

随机获取学生

SELECT * FROM stu ORDER BY RAND() LIMIT 1;

按出生月份从小到大排序

SELECT birthday,MONTH(birthday) as m FROM stu ORDER BY m ASC;

# 或使用字符串函数操作
SELECT birthday,mid(birthday,6,2) as m FROM stu ORDER BY m ASC;

# 自定义排序

field函数用于比较值在集合中的索引,利用这一特性可以自定义排序

SELECT FIELD('a','c','a','b');
# 第一个a 为比较字符,后面的 c/a/b为集合,所以结果为2,如果在集合中不存在为0

使用 field 进行自定义排序

SELECT * FROM stu ORDER BY FIELD(left(sname,1),'何','赵');

# COUNT

统计所有学生人数

SELECT COUNT(*) FROM stu;

所有女生人数

SELECT COUNT(*) FROM stu WHERE sex=2;

统计所有分配班级的学生(count(字段)不会统计null值,使用count(*)时会计算null),所以下面使用具体的字段

SELECT COUNT(class_id) FROM stu;

# MIN/MAX

获取最小的学生出生年份

SELECT year(max(birthday)) from stu;

最大的班级编号

SELECT max(class_id) FROM stu;

获取点击数最少的文章

SELECT * FROM article WHERE click = (SELECT MIN(click) FROM article);

# SUM/AVG

获取所有文章总点击数

SELECT SUM(click) FROM article;

获取平均点击数

SELECT AVG(click) FROM article;

获取低于平均点击数据的文章

SELECT * FROM article WHERE click < (SELECT AVG(click) FROM article);

获取学生的平均年龄

SELECT ROUND(AVG(TIMESTAMPDIFF(YEAR,birthday,now())))  FROM stu ;

# DISTINCT

distinct用于去除结果集中的重复记录

获取所有班级编号

SELECT DISTINCT class_id AS class FROM stu WHERE class_id IS NOT NULL;

获取学生数,同班同名的算一个

SELECT COUNT(DISTINCT class_id,sname) FROM stu WHERE class_id IS NOT NULL;

# GROUP

统计每个班级的人数

SELECT COUNT(*),class_id FROM stu WHERE class_id IS NOT NULL GROUP BY class_id ;

每个班年龄最大的同学

执行下面查询会报错,因为mysql要求select中的列要在group中使用。

可以通过更改查询模式,允许select的列不在group中出现

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

然后查询就可以得到结果

SELECT min(birthday),sname FROM stu GROUP BY class_id;

也可以使用子查询

SELECT * FROM stu where birthday IN(
SELECT min(birthday) FROM stu GROUP BY class_id);

统计每班的男、女人数

SELECT concat(class_id,'班'),if(sex=1,'男','女') as sex,
count(*) FROM stu
WHERE class_id IS NOT NULL
GROUP BY class_id,sex ORDER BY class_id;

查找超过两个同学的班级

SELECT class_id FROM stu GROUP BY class_id HAVING count(*)>2;

查找本周迟到超过两次的同学

SELECT stu_id FROM attendance 
WHERE date(created_at)>date(DATE_ADD(NOW(),INTERVAL 0-WEEKDAY(NOW()) day))
AND time(created_at)>'08:30:00'
GROUP BY stu_id
HAVING COUNT(*)>2;

本周哪个同学准时到校次数最多

SELECT count(*) as c,stu_id FROM attendance
WHERE date(created_at)>=date(date_add(now(),interval 0-WEEKDAY(now()) day))
AND time(created_at)<='08:30:00'
GROUP BY stu_id
ORDER by c desc
limit 1;

本周哪一天迟到的人数最少

SELECT date(created_at) FROM attendance 
WHERE date(created_at)>date(DATE_ADD(NOW(),INTERVAL 0-WEEKDAY(NOW()) day))
AND time(created_at)<='08:30:00'
GROUP BY created_at
ORDER BY COUNT(*) DESC
LIMIT 1;

查找哪个姓的同学最多

SELECT left(sname,1) as s,count(*) AS c FROM stu
GROUP BY s
ORDER BY c DESC
LIMIT 1;

查找超过两个同学的姓氏

SELECT left(sname,1) as s,count(*) AS c FROM stu
GROUP BY s
HAVING c>=2;