92 lines
2.4 KiB
SQL
92 lines
2.4 KiB
SQL
-- 简化版学生数据查询脚本
|
|
-- 当健康数据视图不存在时的备用方案
|
|
|
|
-- 1. 创建一个简单的学生信息视图（不包含健康数据）
|
|
CREATE OR REPLACE VIEW public.v_student_basic_info AS
|
|
SELECT
|
|
u.id as user_id,
|
|
u.username as name,
|
|
COALESCE(u.email, u.username) as student_id, -- 使用email或username作为学号
|
|
u.avatar_url as avatar,
|
|
u.class_id,
|
|
c.name as class_name,
|
|
g.name as grade_name,
|
|
s.name as school_name,
|
|
-- 默认健康数据字段（为了兼容前端）
|
|
NULL::numeric as temperature_c,
|
|
NULL::timestamp as temp_time,
|
|
NULL::numeric as heart_rate_bpm,
|
|
NULL::timestamp as heart_time,
|
|
NULL::numeric as spo2_percent,
|
|
NULL::timestamp as spo2_time,
|
|
NULL::numeric as step_count,
|
|
NULL::timestamp as steps_time
|
|
FROM public.ak_users u
|
|
LEFT JOIN public.ak_classes c ON u.class_id = c.id
|
|
LEFT JOIN public.ak_grades g ON c.grade_id = g.id
|
|
LEFT JOIN public.ak_schools s ON g.school_id = s.id
|
|
WHERE u.role = 'student';
|
|
|
|
-- 2. 检查系统中存在的表
|
|
SELECT
|
|
table_schema,
|
|
table_name,
|
|
table_type
|
|
FROM information_schema.tables
|
|
WHERE table_schema = 'public'
|
|
AND (
|
|
table_name LIKE '%student%' OR
|
|
table_name LIKE '%health%' OR
|
|
table_name LIKE '%sensor%' OR
|
|
table_name LIKE '%ss_%' OR
|
|
table_name = 'ak_users'
|
|
)
|
|
ORDER BY table_name;
|
|
|
|
-- 3. 查看ak_users表的结构
|
|
SELECT
|
|
column_name,
|
|
data_type,
|
|
is_nullable,
|
|
column_default
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'ak_users'
|
|
ORDER BY ordinal_position;
|
|
|
|
-- 4. 测试基础查询：获取某个班级的学生
|
|
-- 这是前端当前使用的查询逻辑
|
|
SELECT
|
|
id,
|
|
username as name,
|
|
email as student_id,
|
|
avatar_url as avatar,
|
|
class_id,
|
|
school_id,
|
|
grade_id,
|
|
role
|
|
FROM public.ak_users
|
|
WHERE role = 'student'
|
|
AND class_id IS NOT NULL
|
|
ORDER BY username
|
|
LIMIT 10;
|
|
|
|
-- 5. 模拟前端查询：假设当前用户属于某个班级
|
|
WITH current_user_class AS (
|
|
-- 模拟当前用户的class_id
|
|
SELECT class_id
|
|
FROM public.ak_users
|
|
WHERE class_id IS NOT NULL
|
|
LIMIT 1
|
|
)
|
|
SELECT
|
|
u.id,
|
|
u.username as name,
|
|
u.email as student_id,
|
|
u.avatar_url as avatar,
|
|
u.class_id
|
|
FROM public.ak_users u
|
|
JOIN current_user_class cuc ON u.class_id = cuc.class_id
|
|
WHERE u.role = 'student'
|
|
ORDER BY u.username;
|