61 lines
1.8 KiB
SQL
61 lines
1.8 KiB
SQL
-- 查询当前数据库中的班级结构
|
|
-- 检查已有的学校、年级、班级数据
|
|
|
|
-- 1. 查看现有的学校数据
|
|
SELECT id, name, type FROM public.ak_schools ORDER BY name;
|
|
|
|
-- 2. 查看现有的年级数据
|
|
SELECT
|
|
g.id,
|
|
g.name as grade_name,
|
|
s.name as school_name
|
|
FROM public.ak_grades g
|
|
LEFT JOIN public.ak_schools s ON g.school_id = s.id
|
|
ORDER BY s.name, g.name;
|
|
|
|
-- 3. 查看现有的班级数据
|
|
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
|
|
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;
|
|
|
|
-- 4. 查看学生的班级分布情况
|
|
SELECT
|
|
COALESCE(c.name, '未分配班级') as class_name,
|
|
COUNT(u.id) as student_count,
|
|
array_agg(u.username) as students
|
|
FROM public.ak_users u
|
|
LEFT JOIN public.ak_classes c ON u.class_id = c.id
|
|
WHERE u.role = 'student'
|
|
GROUP BY c.name
|
|
ORDER BY student_count DESC;
|
|
|
|
-- 5. 查看教师-班级关系（如果表存在）
|
|
SELECT
|
|
u.username as teacher_name,
|
|
c.name as class_name,
|
|
g.name as grade_name,
|
|
s.name as school_name
|
|
FROM public.ak_teacher_roles tr
|
|
JOIN public.ak_users u ON tr.user_id = u.id
|
|
JOIN public.ak_classes c ON tr.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
|
|
ORDER BY u.username, c.name;
|
|
|
|
-- 6. 检查是否有学生未分配班级
|
|
SELECT
|
|
COUNT(*) as total_students,
|
|
COUNT(CASE WHEN class_id IS NOT NULL THEN 1 END) as assigned_students,
|
|
COUNT(CASE WHEN class_id IS NULL THEN 1 END) as unassigned_students
|
|
FROM public.ak_users
|
|
WHERE role = 'student';
|