常用 sql 笔记（sql 必知必会）

---

一、统计查询

1. 关联表数量查询。

    查询各班级学生数量。
   

SELECT
	t1.name,
	t1.class_no,
	t4.student_num
FROM 
	tb_class t1
	LEFT JION (
		SELECT
			t2.id,
			COUNT(t3.id) AS student_num
		FROM
			tb_class t2
			LEFT JION tb_student t3 ON t3.class_id = t2.id
		WHERE
			t2.del_flag = '0'
		GROUP BY
			t2.id
	) t4 ON t4.id = t1.id
WHERE
	t1.del_flag = '0'

2. 单表数量统计

    查询各审核状态数量统计。
   

SELECT
	IFNULL(SUM(CASE WHEN t1.audit_status='audit_wait' THEN 1 ELSE 0 END), 0) AS audit_wait_num,
	IFNULL(SUM(CASE WHEN t1.audit_status='audit_pass' THEN 1 else 0 end), 0) AS audit_pass_num,
	IFNULL(SUM(CASE WHEN t1.audit_status='audit_reject' THEN 1 else 0 end), 0) AS audit_reject_num
FROM 
	tb_audit_log t1
WHERE
	t1.del_flag = '0'