SQL查询返回所有科目成绩而非指定科目问题排查与修正

我编写了一条用于返回某次考试中指定科目学生成绩的SQL查询，但实际返回了所有科目的成绩。相关数据库表结构、测试数据及当前查询语句如下：

数据库表结构与测试数据 #

CREATE TABLE `exam_groups` (
  `id` int(11) NOT NULL,
  `name` varchar(250) DEFAULT NULL,
  `exam_type` varchar(250) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `is_active` int(11) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


INSERT INTO `exam_groups` (`id`, `name`, `exam_type`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'PRE-NURSERY EXAMINATION', 'prekg_system', 'This section contains all examinations pertaining to the pre-nursery section', 0, '2022-11-21 19:11:56', NULL),
(2, 'KG EXAMINATION', 'kg_system', 'This is a section that contains examinations related to KG school', 0, '2022-11-21 19:12:19', NULL),
(3, 'PRIMARY EXAMINATION', 'primary_system', 'This section contains all examinations for Primary Secondary School.', 0, '2023-05-26 14:30:09', NULL),
(4, 'JSS  EXAMINATION', 'jss_system', 'This section contains all examinations for Junior Secondary School.', 0, '2022-11-21 19:13:33', NULL),
(5, 'SSS EXAMINATION', 'sss_system', 'This section contains all examinations for Senior Secondary School.', 0, '2022-11-21 19:14:04', NULL);

CREATE TABLE `exam_group_class_batch_exams` (
  `id` int(11) NOT NULL,
  `exam` varchar(250) DEFAULT NULL,
  `passing_percentage` float(10,2) DEFAULT NULL,
  `session_id` int(11) NOT NULL,
  `sch_open` varchar(250) DEFAULT NULL,
  `next_term_begin` varchar(250) DEFAULT NULL,
  `documents` varchar(100) DEFAULT NULL,
  `date_from` date DEFAULT NULL,
  `date_to` date DEFAULT NULL,
  `exam_group_id` int(11) DEFAULT NULL,
  `is_first` int(11) NOT NULL DEFAULT 0,
  `is_firstmid` int(11) NOT NULL DEFAULT 0,
  `is_sec` int(11) NOT NULL DEFAULT 0,
  `is_secmid` int(11) NOT NULL DEFAULT 0,
  `is_third` int(11) NOT NULL DEFAULT 0,
  `is_thirdmid` int(11) NOT NULL DEFAULT 0,
  `use_exam_roll_no` int(11) NOT NULL DEFAULT 1,
  `is_publish` int(11) DEFAULT 0,
  `is_rank_generated` int(11) NOT NULL DEFAULT 0,
  `description` text DEFAULT NULL,
  `is_active` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `exam_group_class_batch_exams` (`id`, `exam`, `passing_percentage`, `session_id`, `sch_open`, `next_term_begin`, `documents`, `date_from`, `date_to`, `exam_group_id`, `is_first`, `is_firstmid`, `is_sec`, `is_secmid`, `is_third`, `is_thirdmid`, `use_exam_roll_no`, `is_publish`, `is_rank_generated`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
(8, 'FIRST MID-TERM EXAMINATION', NULL, 17, '110', '2ND MAY, 2023', NULL, NULL, NULL, 1, 0, 1, 0, 0, 0, 0, 1, 1, 0, 'Mid-term examination for first term.', 1, '2023-04-07 15:07:14', NULL),
(9, 'FIRST MID-TERM EXAMINATION', NULL, 17, '', '', NULL, NULL, NULL, 2, 0, 1, 0, 0, 0, 0, 1, 1, 0, 'Mid-term examination for first term.', 1, '2022-10-20 21:05:03', NULL);

CREATE TABLE `exam_group_class_batch_exam_students` (
  `id` int(11) NOT NULL,
  `exam_group_class_batch_exam_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `student_session_id` int(11) NOT NULL,
  `roll_no` int(11) DEFAULT NULL,
  `no_atten` int(11) NOT NULL,
  `no_abs` int(11) NOT NULL,
  `teacher_remark` text DEFAULT NULL,
  `rank` int(11) NOT NULL DEFAULT 0,
  `principal_remark` text DEFAULT NULL,
  `is_active` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `exam_group_class_batch_exam_students` (`id`, `exam_group_class_batch_exam_id`, `student_id`, `student_session_id`, `roll_no`, `no_atten`, `no_abs`, `teacher_remark`, `rank`, `principal_remark`, `is_active`, `created_at`, `updated_at`) VALUES
(3, 8, 4816, 7155, 0, 0, 0, NULL, 0, NULL, 0, '2022-10-08 15:18:38', NULL),
(4, 8, 4847, 7332, 0, 0, 0, NULL, 0, NULL, 0, '2022-10-08 15:18:38', NULL);

CREATE TABLE `exam_group_class_batch_exam_subjects` (
  `id` int(11) NOT NULL,
  `exam_group_class_batch_exams_id` int(11) DEFAULT NULL,
  `subject_id` int(11) NOT NULL,
  `date_from` date NOT NULL,
  `time_from` time NOT NULL,
  `duration` varchar(50) NOT NULL,
  `room_no` varchar(100) DEFAULT NULL,
  `max_marks` float(10,2) DEFAULT NULL,
  `min_marks` float(10,2) DEFAULT NULL,
  `credit_hours` float(10,2) DEFAULT 0.00,
  `date_to` datetime DEFAULT NULL,
  `is_active` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `exam_group_class_batch_exam_subjects` (`id`, `exam_group_class_batch_exams_id`, `subject_id`, `date_from`, `time_from`, `duration`, `room_no`, `max_marks`, `min_marks`, `credit_hours`, `date_to`, `is_active`, `created_at`, `updated_at`) VALUES
(91, 8, 116, '2022-08-25', '10:13:01', '0', '', 1.00, 1.00, 0.00, NULL, 0, '2022-08-25 09:14:02', NULL),
(92, 8, 43, '2022-08-25', '10:13:05', '0', '', 1.00, 1.00, 0.00, NULL, 0, '2022-08-25 09:14:02', NULL);

CREATE TABLE `exam_group_exam_results` (
  `id` int(11) NOT NULL,
  `exam_group_class_batch_exam_student_id` int(11) NOT NULL,
  `exam_group_class_batch_exam_subject_id` int(11) DEFAULT NULL,
  `exam_group_student_id` int(11) DEFAULT NULL,
  `attendence` varchar(10) DEFAULT NULL,
  `get_ca1` int(11) DEFAULT NULL,
  `get_ca2` int(11) DEFAULT NULL,
  `get_ca3` int(11) DEFAULT NULL,
  `get_ca4` int(11) DEFAULT NULL,
  `get_ca5` int(11) DEFAULT NULL,
  `get_ca6` int(11) DEFAULT NULL,
  `get_exam` int(11) DEFAULT NULL,
  `get_tot_score` int(11) DEFAULT NULL,
  `rem1` text DEFAULT NULL,
  `rem2` text DEFAULT NULL,
  `note` text DEFAULT NULL,
  `get_marks` int(11) DEFAULT NULL,
  `is_active` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `exam_group_exam_results` (`id`, `exam_group_class_batch_exam_student_id`, `exam_group_class_batch_exam_subject_id`, `exam_group_student_id`, `attendence`, `get_ca1`, `get_ca2`, `get_ca3`, `get_ca4`, `get_ca5`, `get_ca6`, `get_exam`, `get_tot_score`, `rem1`, `rem2`, `note`, `get_marks`, `is_active`, `created_at`, `updated_at`) VALUES
(863, 35, 115, NULL, 'present', 8, 10, 4, 4, 0, 0, 0, 26, NULL, NULL, NULL, NULL, 0, '2022-10-25 08:20:27', NULL),
(864, 44, 115, NULL, 'present', 10, 10, 5, 5, 0, 0, 0, 30, NULL, NULL, NULL, NULL, 0, '2022-10-25 08:20:27', NULL);

CREATE TABLE `subjects` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `code` varchar(100) NOT NULL,
  `type` varchar(100) NOT NULL,
  `is_active` varchar(255) DEFAULT 'no',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_at` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO `subjects` (`id`, `name`, `code`, `type`, `is_active`, `created_at`, `updated_at`) VALUES
(39, 'AGRICULTURAL SCIENCE', 'AGRIC', 'Theory', 'no', '2019-09-29 17:34:11', '0000-00-00'),
(43, 'BASIC SCIENCE & TECHNOLOGY', 'B.S.T', 'theory', 'no', '2022-12-13 12:54:23', '0000-00-00');

当前查询语句 #

SELECT DISTINCT
    es.student_id AS exam_student_id,
    s.firstname AS student_firstname,
    s.lastname AS student_lastname,
    sub.name AS subject_name,
    eca.get_ca1 AS ca1_marks,
    eca.get_ca2 AS ca2_marks,
    eca.get_ca3 AS ca3_marks,
    eca.get_ca4 AS ca4_marks,
    eca.get_ca5 AS ca5_marks,
    eca.get_ca6 AS ca6_marks,
    eca.get_exam AS exam_marks,
    eca.get_tot_score AS total_score,
    eca.rem1 AS remark1,
    eca.rem2 AS remark2
FROM
    exam_group_class_batch_exam_students es
JOIN
    exam_group_class_batch_exams egcbe ON es.exam_group_class_batch_exam_id = egcbe.id
JOIN
    exam_group_class_batch_exam_subjects egcbes ON egcbe.id = egcbes.exam_group_class_batch_exams_id
JOIN
    subjects sub ON egcbes.subject_id = sub.id
JOIN
    exam_group_exam_results eca ON es.id = eca.exam_group_class_batch_exam_student_id
JOIN
    students s ON es.student_id = s.id
JOIN
    student_session ss ON s.id = ss.student_id
JOIN
    classes c ON ss.class_id = c.id
JOIN
    sections sec ON ss.section_id = sec.id
JOIN
    exam_groups eg ON egcbe.exam_group_id = eg.id
WHERE
    ss.session_id = 19 
    AND c.id = 12 
    AND sec.id = 6
    AND sub.id = 43  -- Filtering by the specific subject ID
    AND eg.id = 4 
    AND egcbe.id = 80
ORDER BY
    s.lastname, s.firstname, sub.name;

问题详情 #

尽管在WHERE子句中指定了sub.id = 43，查询仍返回所有科目的成绩，而非仅指定科目。已尝试确认JOIN关联逻辑正确性、添加DISTINCT关键字去重，但问题未解决。

• 期望结果：仅返回subject_id为43的科目成绩
• 实际结果：返回所有科目成绩

问题原因 #

核心问题是成绩表与科目表未建立关联：
你的查询中，exam_group_exam_results（成绩记录表）仅通过exam_group_class_batch_exam_student_id关联到学生考试表，没有和exam_group_class_batch_exam_subjects（考试科目关联表）绑定。这导致：

• 虽然sub.id=43过滤了要显示的科目名称，但成绩记录并没有和该科目关联，最终会把该学生所有科目的成绩都拉出来，只是统一显示成科目43的名称，看起来像是返回了所有科目，实际是数据关联错误导致的不匹配结果。

修改后的查询语句 #

需要在exam_group_exam_results和exam_group_class_batch_exam_subjects之间添加关联条件，确保成绩只对应指定科目：

SELECT DISTINCT
    es.student_id AS exam_student_id,
    s.firstname AS student_firstname,
    s.lastname AS student_lastname,
    sub.name AS subject_name,
    eca.get_ca1 AS ca1_marks,
    eca.get_ca2 AS ca2_marks,
    eca.get_ca3 AS ca3_marks,
    eca.get_ca4 AS ca4_marks,
    eca.get_ca5 AS ca5_marks,
    eca.get_ca6 AS ca6_marks,
    eca.get_exam AS exam_marks,
    eca.get_tot_score AS total_score,
    eca.rem1 AS remark1,
    eca.rem2 AS remark2
FROM
    exam_group_class_batch_exam_students es
JOIN
    exam_group_class_batch_exams egcbe ON es.exam_group_class_batch_exam_id = egcbe.id
JOIN
    exam_group_class_batch_exam_subjects egcbes ON egcbe.id = egcbes.exam_group_class_batch_exams_id
JOIN
    subjects sub ON egcbes.subject_id = sub.id
JOIN
    exam_group_exam_results eca ON es.id = eca.exam_group_class_batch_exam_student_id 
        AND egcbes.id = eca.exam_group_class_batch_exam_subject_id -- 新增：关联成绩与对应科目
JOIN
    students s ON es.student_id = s.id
JOIN
    student_session ss ON s.id = ss.student_id
JOIN
    classes c ON ss.class_id = c.id
JOIN