1.分组

# 典型分组
SELECT
	class_name,
	COUNT(*)
FROM student
GROUP BY class_name;

# 按处理后的字段分组1
SELECT
	IF(class_name LIKE '尖子%','尖子班','普通班'),
	COUNT(*)
FROM student
GROUP BY 
	IF(class_name LIKE '尖子%','尖子班','普通班');
# 按处理后的字段分组2
SELECT
	DATE_FORMAT(birth_date,'%Y-%m'),
	COUNT(*)
FROM student
GROUP BY DATE_FORMAT(birth_date,'%Y-%m');
# 按处理后的字段分组3
SELECT
	(CASE WHEN score BETWEEN 90 AND 100 THEN '优秀'
		  WHEN score BETWEEN 80 AND 89 THEN '良好'
		  WHEN score BETWEEN 60 AND 79 THEN '普通'
		  ELSE '不及格'
	END),
	COUNT(*)
FROM student
GROUP BY 
	(CASE WHEN score BETWEEN 90 AND 100 THEN '优秀'
		  WHEN score BETWEEN 80 AND 89 THEN '良好'
		  WHEN score BETWEEN 60 AND 79 THEN '普通'
		  ELSE '不及格'
	END);

# 输出非分组且非聚合的字段
SELECT
	ANY_VALUE(class_id),# 该字段不是分组字段也不是聚合字段，因此需要特殊处理
	class_name,
	COUNT(*)
FROM student
GROUP BY class_name;

# 带总计行的分组（如果多层分组，还会有小计行）
SELECT
	ANY_VALUE(class_id),
	IF(GROUPING(class_name),'总计',class_name)# 将总计行组名修改为总计，GROUPING用于判断是否为总计行
	COUNT(*)
FROM student
GROUP BY class_name WITH ROLLUP;# 总计行默认组名为NULL

2.排序

# 普通排序
SELECT *
FROM student
ORDER BY class_name ASC;

# 分组排序
SELECT *
FROM student
ORDER BY 
	IF(class_name LIKE '尖子%','尖子班','普通班') ASC,score DESC;

# 分组后同字段的不同排序
SELECT *
FROM student
ORDER BY 
	IF(class_name LIKE '尖子%',score,NULL) DESC,
	IF(class_name NOT LIKE '尖子%',score,NULL) ASC;

更多内容见专栏