mysql - mysql查询从多个表中获取记录并加入

我有5个表，分别命名为：学校，候选人，候选人主题，科目，lgas
每所学校都属于一个lga，每位候选人都属于一所学校，每位候选人都注册科目
下面是表结构：

CREATE TABLE `subjects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `code` varchar(10) DEFAULT NULL,
  `exam_type_id` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `type` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=latin1;

CREATE TABLE `candidates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `exam_no` varchar(20) DEFAULT NULL,
  `surname` varchar(100) DEFAULT NULL,
  `other_names` varchar(150) DEFAULT NULL,
  `school_id` int(11) DEFAULT NULL,
  `exam_type_id` int(11) DEFAULT NULL,
  `dob` varchar(12) DEFAULT NULL,
  `sex` varchar(6) DEFAULT NULL,
  `no_of_subjects` int(2) DEFAULT NULL,
  `nationality` varchar(20) DEFAULT NULL,
  `state` varchar(20) DEFAULT NULL,
  `lga` varchar(20) DEFAULT NULL,
  `exam_year` varchar(4) DEFAULT NULL,
  `date_created` varchar(255) DEFAULT NULL,
  `date_modified` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `registration_completed` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28034 DEFAULT CHARSET=latin1;

CREATE TABLE `candidate_subjects` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `candidate_id` int(11) DEFAULT NULL,
  `exam_type_id` int(10) DEFAULT NULL,
  `subject_id` int(10) DEFAULT NULL,
  `ca_score` int(11) DEFAULT NULL,
  `exam_score` int(6) DEFAULT NULL,
  `score_grade` varchar(10) DEFAULT NULL,
  `date_created` varchar(10) DEFAULT NULL,
  `date_modified` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=327740 DEFAULT CHARSET=latin1;

CREATE TABLE `schools` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `passcode` varchar(15) DEFAULT NULL,
  `code` varchar(10) DEFAULT NULL,
  `name` varchar(200) DEFAULT NULL,
  `lga` int(11) DEFAULT NULL,
  `address` varchar(250) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `exam_year` varchar(10) DEFAULT NULL,
  `eo_name` varchar(100) DEFAULT NULL,
  `eo_phone` varchar(50) DEFAULT NULL,
  `eo_email` varchar(100) DEFAULT NULL,
  `date_created` varchar(10) DEFAULT NULL,
  `date_modified` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `profile_created` int(1) DEFAULT NULL,
  `entries_purchased` int(11) DEFAULT NULL,
  `entries_used` int(11) DEFAULT NULL,
  `entries_remaining` int(11) DEFAULT NULL,
  `scratchcard_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=652 DEFAULT CHARSET=latin1;

CREATE TABLE `lgas` (
  `lga_id` int(11) NOT NULL AUTO_INCREMENT,
  `lga_name` varchar(250) NOT NULL,
  `state_id` varchar(20) NOT NULL,
  PRIMARY KEY (`lga_id`)
) ENGINE=InnoDB AUTO_INCREMENT=786 DEFAULT CHARSET=latin1;

我想为每个lga生成一个报告，如下所示：

S / No |学校名称|英文| Mth | B.sc | ....注册总数

那就是所选lga中的学校名称，即该学校注册Eng，Mth，B.sc的学生总数...该所学校注册这些科目的学生总数

最佳答案

SELECT
  b.name as school_name,
  sum(case when e.name = 'Eng' then 1 else 0 end) as english_students,
  sum(case when e.name = 'Mth' then 1 else 0 end) as math_students,
  sum(case when e.name = 'B.sc' then 1 else 0 end) as whatever_this_is_students
FROM lgas a
LEFT JOIN schools b ON a.lga_id = b.lga
LEFT JOIN candidates c on b.id = c.school_id
LEFT JOIN candidate_subjects d on c.id = d.candidate_id
LEFT JOIN subjects e on d.subject_id = e.id
WHERE a.lda_id = 'selected_id'
GROUP by school_name;