127 lines
3.3 KiB
SQL
127 lines
3.3 KiB
SQL
-- 测试当前用户class_id功能的验证脚本
|
||
|
||
-- 1. 检查ak_users表中用户的class_id设置情况
|
||
SELECT
|
||
id,
|
||
username,
|
||
email,
|
||
role,
|
||
school_id,
|
||
grade_id,
|
||
class_id
|
||
FROM public.ak_users
|
||
WHERE role IN ('teacher', 'student')
|
||
ORDER BY role, username;
|
||
|
||
-- 2. 检查每个班级的学生分布
|
||
SELECT
|
||
c.id as class_id,
|
||
c.name as class_name,
|
||
g.name as grade_name,
|
||
s.name as school_name,
|
||
COUNT(u.id) as student_count,
|
||
array_agg(u.username) as student_names
|
||
FROM public.ak_classes c
|
||
LEFT JOIN public.ak_grades g ON c.grade_id = g.id
|
||
LEFT JOIN public.ak_schools s ON g.school_id = s.id
|
||
LEFT JOIN public.ak_users u ON u.class_id = c.id AND u.role = 'student'
|
||
GROUP BY c.id, c.name, g.name, s.name
|
||
ORDER BY s.name, g.name, c.name;
|
||
|
||
-- 3. 检查是否有用户（老师或学生）分配到了同一个班级
|
||
SELECT
|
||
class_id,
|
||
COUNT(CASE WHEN role = 'teacher' THEN 1 END) as teacher_count,
|
||
COUNT(CASE WHEN role = 'student' THEN 1 END) as student_count,
|
||
array_agg(CASE WHEN role = 'teacher' THEN username END) as teachers,
|
||
array_agg(CASE WHEN role = 'student' THEN username END) as students
|
||
FROM public.ak_users
|
||
WHERE class_id IS NOT NULL
|
||
GROUP BY class_id
|
||
ORDER BY class_id;
|
||
|
||
-- 4. 模拟前端功能：假设当前登录用户有class_id，查询同班级学生
|
||
-- 这里以第一个有class_id的用户为例
|
||
WITH current_user_class AS (
|
||
SELECT class_id
|
||
FROM public.ak_users
|
||
WHERE class_id IS NOT NULL
|
||
LIMIT 1
|
||
)
|
||
SELECT
|
||
u.id,
|
||
u.username,
|
||
u.role,
|
||
u.class_id,
|
||
c.name as class_name
|
||
FROM public.ak_users u
|
||
JOIN current_user_class cuc ON u.class_id = cuc.class_id
|
||
LEFT JOIN public.ak_classes c ON u.class_id = c.id
|
||
WHERE u.role = 'student'
|
||
ORDER BY u.username;
|
||
|
||
-- 5. 检查v_latest_student_health视图是否存在并能正常查询
|
||
-- 如果视图不存在，这个查询会报错
|
||
SELECT
|
||
user_id,
|
||
name,
|
||
temperature_c,
|
||
heart_rate_bpm,
|
||
spo2_percent,
|
||
step_count
|
||
FROM public.v_latest_student_health
|
||
LIMIT 5;
|
||
|
||
-- 6. 完整的模拟查询：模拟前端的完整逻辑
|
||
-- 假设当前用户的class_id是第一个有class_id的用户
|
||
WITH current_user_info AS (
|
||
SELECT id, username, class_id
|
||
FROM public.ak_users
|
||
WHERE class_id IS NOT NULL
|
||
LIMIT 1
|
||
),
|
||
same_class_students AS (
|
||
SELECT u.id, u.username, u.student_id, u.avatar
|
||
FROM public.ak_users u
|
||
JOIN current_user_info cui ON u.class_id = cui.class_id
|
||
WHERE u.role = 'student'
|
||
)
|
||
SELECT
|
||
scs.id,
|
||
scs.username,
|
||
scs.student_id,
|
||
vh.temperature_c,
|
||
vh.heart_rate_bpm,
|
||
vh.spo2_percent,
|
||
vh.step_count,
|
||
-- 模拟异常判断
|
||
CASE
|
||
WHEN vh.temperature_c < 36.0 OR vh.temperature_c > 37.5 THEN 1
|
||
ELSE 0
|
||
END +
|
||
CASE
|
||
WHEN vh.heart_rate_bpm < 60 OR vh.heart_rate_bpm > 100 THEN 1
|
||
ELSE 0
|
||
END +
|
||
CASE
|
||
WHEN vh.spo2_percent < 95 THEN 1
|
||
ELSE 0
|
||
END as abnormal_count
|
||
FROM same_class_students scs
|
||
LEFT JOIN public.v_latest_student_health vh ON scs.id = vh.user_id
|
||
ORDER BY
|
||
-- 异常优先排序
|
||
(CASE
|
||
WHEN vh.temperature_c < 36.0 OR vh.temperature_c > 37.5 THEN 1
|
||
ELSE 0
|
||
END +
|
||
CASE
|
||
WHEN vh.heart_rate_bpm < 60 OR vh.heart_rate_bpm > 100 THEN 1
|
||
ELSE 0
|
||
END +
|
||
CASE
|
||
WHEN vh.spo2_percent < 95 THEN 1
|
||
ELSE 0
|
||
END) DESC,
|
||
scs.username;
|