求助：将含自连接子查询的MySQL语句转为CodeIgniter查询构建器

没问题，我帮你把这条MySQL查询转换成CodeIgniter的查询构建器语法，先理清楚原查询的逻辑：它通过嵌套子查询先提取每个employee_id对应的created_on，再和原answers表关联筛选出对应记录，最后按创建时间倒序排列。

CodeIgniter 3 实现方式 #

// 构建最内层子查询：获取employee_id和created_on并重命名
$subquery1 = $this->db->select('employee_id as test_employee_id, created_on as test_created_on')
                      ->get_compiled_select('answers');

// 构建中间层子查询：按employee_id分组
$subquery2 = $this->db->select('*')
                      ->from("($subquery1) as test")
                      ->group_by('test.test_employee_id')
                      ->get_compiled_select();

// 主查询：关联原表和子查询，筛选并排序
$query = $this->db->select('answers.*')
                  ->from('answers')
                  ->join("($subquery2) as test", "test.test_employee_id = answers.employee_id AND test.test_created_on = answers.created_on", 'inner')
                  ->order_by('answers.created_on', 'DESC')
                  ->get();

// 获取最终结果
$results = $query->result();

CodeIgniter 4 实现方式 #

如果你用的是CodeIgniter 4，写法类似，只是调用方式略有不同：

$db = \Config\Database::connect();

// 最内层子查询
$subquery1 = $db->table('answers')
                ->select('employee_id as test_employee_id, created_on as test_created_on')
                ->compileSelect();

// 中间层子查询
$subquery2 = $db->table("($subquery1) as test")
                ->select('*')
                ->groupBy('test.test_employee_id')
                ->compileSelect();

// 主查询
$query = $db->table('answers')
            ->select('answers.*')
            ->join("($subquery2) as test", "test.test_employee_id = answers.employee_id AND test.test_created_on = answers.created_on", 'inner')
            ->orderBy('answers.created_on', 'DESC')
            ->get();

$results = $query->getResult();

优化建议（可选） #

原查询的分组逻辑在MySQL严格模式下可能会触发警告（因为test_created_on没有被聚合或包含在分组字段中）。如果你的需求是获取每个员工最新的回答记录，可以优化子查询，用MAX(created_on)明确获取每个员工的最新创建时间，这样更符合SQL规范：

优化后的MySQL语句：

SELECT answers.* 
FROM answers 
INNER JOIN (
    SELECT employee_id as test_employee_id, MAX(created_on) as test_created_on 
    FROM answers 
    GROUP BY employee_id
) AS test ON test.test_employee_id = answers.employee_id AND test.test_created_on = answers.created_on 
ORDER BY answers.created_on DESC

对应的CodeIgniter 3简化写法：

$subquery = $this->db->select('employee_id as test_employee_id, MAX(created_on) as test_created_on')
                     ->from('answers')
                     ->group_by('employee_id')
                     ->get_compiled_select();

$query = $this->db->select('answers.*')
                  ->from('answers')
                  ->join("($subquery) as test", "test.test_employee_id = answers.employee_id AND test.test_created_on = answers.created_on", 'inner')
                  ->order_by('answers.created_on', 'DESC')
                  ->get();

$results = $query->result();

内容的提问来源于stack exchange，提问作者danicaLuxx