数据清洗部分方式

1# 非空判断方式之一
SELECT
	ei.mat_number,
	ei.is_status,
	NULLIF( ei.is_status, '' ) AS valid_date 
FROM
	_info ei 
WHERE
	NULLIF( ei.is_status, '' ) IS NOT NULL;

2# Mysql
SELECT
	* 
FROM
	orders 
ORDER BY
	created_at DESC 
	LIMIT 1;

3# 达梦
SELECT
	* 
FROM
	your_table 
ORDER BY
	your_timestamp_column 
	DESC FETCH FIRST 1 ROW ONLY

4# 达梦/mysql递归
WITH RECURSIVE DepartmentHierarchy (dept_id, parent_dept_id, dept_name, level) AS (
    -- 初始化部分：获取顶级部门
    SELECT dept_id, parent_dept_id, dept_name, 1
    FROM departments
    WHERE parent_dept_id IS NULL

    UNION ALL

    -- 递归部分：获取子部门
    SELECT d.dept_id, d.parent_dept_id, d.dept_name, h.level + 1
    FROM departments d
    INNER JOIN DepartmentHierarchy h ON d.parent_dept_id = h.dept_id
)
SELECT dept_id, parent_dept_id, dept_name, level
FROM DepartmentHierarchy;

5# 例子 mysql统计一个学校每个班级语数外90分以上的个数

SELECT
	class_id,
	SUM( CASE WHEN SUBJECT = 'Chinese' AND score >= 90 THEN 1 ELSE 0 END ) AS chinese_count,
	SUM( CASE WHEN SUBJECT = 'Math' AND score >= 90 THEN 1 ELSE 0 END ) AS math_count,
	SUM( CASE WHEN SUBJECT = 'English' AND score >= 90 THEN 1 ELSE 0 END ) AS english_count 
FROM
	grades 
GROUP BY
	class_id;

6# 例二
mysql统计一个学校每个班级语数外90分以上的个数占比

假设你有一个名为 grades 的表，其中包含学生的成绩信息，表结构如下：
student_id：学生 ID。
class_id：班级 ID。
subject：科目名称（如 “Chinese”、“Math”、“English”）。
score：分数。

WITH SubjectCounts AS (
    SELECT
        class_id,
        COUNT(*) FILTER (WHERE subject = 'Chinese' AND score >= 90) AS chinese_count,
        COUNT(*) FILTER (WHERE subject = 'Math' AND score >= 90) AS math_count,
        COUNT(*) FILTER (WHERE subject = 'English' AND score >= 90) AS english_count,
        COUNT(DISTINCT student_id) AS total_students
    FROM grades
    GROUP BY class_id
),
SubjectRatios AS (
    SELECT
        class_id,
        chinese_count,
        math_count,
        english_count,
        total_students,
        chinese_count / total_students * 100.0 AS chinese_ratio,
        math_count / total_students * 100.0 AS math_ratio,
        english_count / total_students * 100.0 AS english_ratio
    FROM SubjectCounts
)
SELECT
    class_id,
    chinese_count,
    math_count,
    english_count,
    total_students,
    ROUND(chinese_ratio, 2) AS chinese_ratio,
    ROUND(math_ratio, 2) AS math_ratio,
    ROUND(english_ratio, 2) AS english_ratio
FROM SubjectRatios;

解释
WITH 子句：定义了一个名为 SubjectCounts 的公共表表达式（CTE），用于计算每个班级中各个科目成绩在 90 分以上的个数以及总的学生人数。
COUNT() FILTER (WHERE subject = ‘Chinese’ AND score >= 90)：统计语文成绩在 90 分以上的个数。
COUNT() FILTER (WHERE subject = ‘Math’ AND score >= 90)：统计数学成绩在 90 分以上的个数。
COUNT(*) FILTER (WHERE subject = ‘English’ AND score >= 90)：统计英语成绩在 90 分以上的个数。
COUNT(DISTINCT student_id)：统计每个班级的总学生人数。
第二个 WITH 子句：定义了一个名为 SubjectRatios 的 CTE，用于计算各个科目的成绩占比。
chinese_count / total_students * 100.0 AS chinese_ratio：计算语文成绩在 90 分以上的占比。
math_count / total_students * 100.0 AS math_ratio：计算数学成绩在 90 分以上的占比。
english_count / total_students * 100.0 AS english_ratio：计算英语成绩在 90 分以上的占比。
最终 SELECT 语句：从 SubjectRatios CTE 中选择所需的结果，并使用 ROUND 函数将百分比结果保留两位小数。