如何使用CodeIgniter查询构造器统计各分组的男女生及总人数

架构老林

2026-9-23

解决方案

核心逻辑

你之前的查询添加了where条件限定单性别，导致查询范围被过滤，只能返回单一性别的统计结果。改用条件聚合写法，通过SUM + CASE WHEN的组合就能在单条查询中同时统计不同性别、总人数的指标。

对应的原生SQL

SELECT 
    tbl_section.section_name,
    COUNT(tbl_student.stud_id) AS total_students,
    SUM(CASE WHEN tbl_student.gender = 'male' THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN tbl_student.gender = 'female' THEN 1 ELSE 0 END) AS female_count
FROM tbl_student
JOIN tbl_section ON tbl_student.section_id = tbl_section.section_id
GROUP BY tbl_section.section_id, tbl_section.section_name

CodeIgniter 查询构造器写法

注意select方法第二个参数传false，避免框架自动转义CASE语句导致语法错误：

$sectionStats = $this->db
    ->select("
        tbl_section.section_name,
        COUNT(tbl_student.stud_id) AS total_students,
        SUM(CASE WHEN tbl_student.gender = 'male' THEN 1 ELSE 0 END) AS male_count,
        SUM(CASE WHEN tbl_student.gender = 'female' THEN 1 ELSE 0 END) AS female_count
    ", false)
    ->from('tbl_student')
    ->join('tbl_section', 'tbl_student.section_id = tbl_section.section_id')
    ->group_by(['tbl_section.section_id', 'tbl_section.section_name'])
    ->get()
    ->result_array();