用CodeIgniter+MySQL展示收支记录时SQL语法错误求助

我来帮你拆解下问题所在，然后给出具体的修复方案：

核心错误原因 #

从你提供的报错信息和生成的SQL来看，有两个关键问题直接导致了语法错误和逻辑偏差：

1. 缺少FROM子句：你的SQL在SELECT完所有字段后直接拼接LEFT JOIN，完全没有指定主表的FROM声明，这是触发MariaDB 1064语法错误的直接原因。
2. 逻辑错误：错误合并表字段：你把payment_details和receipt_details的所有字段放在同一个SELECT语句中，这会导致两张表做笛卡尔积关联，完全不符合"在单个表格展示收支记录"的需求——收支记录应该是两条独立的行（一条支出、一条收入），而不是把两个表的字段强行拼在同一行。

正确实现思路 #

要实现收支记录在同一表格展示，你需要用UNION ALL（或UNION，如需去重）将支出表和收入表的记录合并成一个统一结果集，再关联vendor和site表。具体步骤：

• 分别查询支出、收入表，给相同含义的字段起一致别名，同时添加标记字段区分收支类型
• 用UNION ALL合并两个查询结果
• 将合并后的结果作为子查询，关联供应商和站点表
• 在子查询中添加日期范围过滤（性能优于合并后过滤）

CodeIgniter 模型代码示例 #

public function get_income_expense_records($start_date, $end_date)
{
    // 构建支出记录查询
    $this->db->select('
        payment_details.id AS record_id,
        payment_details.payment_no AS document_no,
        payment_details.site_id,
        payment_details.vendor_id,
        payment_details.work,
        payment_details.description,
        payment_details.date,
        payment_details.amount,
        payment_details.tax_type,
        payment_details.tax,
        payment_details.subtotal,
        payment_details.grand_total,
        payment_details.created_on,
        "expense" AS record_type
    ');
    $this->db->from('payment_details');
    $this->db->where('payment_details.created_on >=', $start_date);
    $this->db->where('payment_details.created_on <=', $end_date);
    $expense_sql = $this->db->get_compiled_select();

    // 构建收入记录查询
    $this->db->select('
        receipt_details.id AS record_id,
        receipt_details.receipt_no AS document_no,
        receipt_details.site_id,
        receipt_details.vendor_id,
        receipt_details.work,
        receipt_details.description,
        receipt_details.date,
        receipt_details.amount,
        receipt_details.tax_type,
        receipt_details.tax,
        receipt_details.subtotal,
        receipt_details.grand_total,
        receipt_details.created_on,
        "income" AS record_type
    ');
    $this->db->from('receipt_details');
    $this->db->where('receipt_details.created_on >=', $start_date);
    $this->db->where('receipt_details.created_on <=', $end_date);
    $income_sql = $this->db->get_compiled_select();

    // 合并查询并关联供应商、站点表
    $final_sql = "
        SELECT 
            merged.*,
            vendor.vname,
            site.sname
        FROM (
            $expense_sql
            UNION ALL
            $income_sql
        ) AS merged
        LEFT JOIN vendor ON vendor.id = merged.vendor_id
        LEFT JOIN site ON site.id = merged.site_id
        ORDER BY merged.created_on DESC
    ";

    // 执行查询并返回结果
    return $this->db->query($final_sql)->result();
}

对应生成的正确SQL（格式化后） #

SELECT 
    merged.*,
    vendor.vname,
    site.sname
FROM (
    SELECT 
        payment_details.id AS record_id,
        payment_details.payment_no AS document_no,
        payment_details.site_id,
        payment_details.vendor_id,
        payment_details.work,
        payment_details.description,
        payment_details.date,
        payment_details.amount,
        payment_details.tax_type,
        payment_details.tax,
        payment_details.subtotal,
        payment_details.grand_total,
        payment_details.created_on,
        "expense" AS record_type
    FROM payment_details
    WHERE payment_details.created_on >= '2018-05-01' AND payment_details.created_on <= '2018-05-31'
    UNION ALL
    SELECT 
        receipt_details.id AS record_id,
        receipt_details.receipt_no AS document_no,
        receipt_details.site_id,
        receipt_details.vendor_id,
        receipt_details.work,
        receipt_details.description,
        receipt_details.date,
        receipt_details.amount,
        receipt_details.tax_type,
        receipt_details.tax,
        receipt_details.subtotal,
        receipt_details.grand_total,
        receipt_details.created_on,
        "income" AS record_type
    FROM receipt_details
    WHERE receipt_details.created_on >= '2018-05-01' AND receipt_details.created_on <= '2018-05-31'
) AS merged
LEFT JOIN vendor ON vendor.id = merged.vendor_id
LEFT JOIN site ON site.id = merged.site_id
ORDER BY merged.created_on DESC

额外说明 #

• 使用UNION ALL而非UNION：UNION会自动去重，而收支记录都是独立的，UNION ALL性能更优
• 统一字段别名：让支出和收入表的对应字段名称一致，方便前端统一展示
• 添加record_type标记：明确区分当前记录是收入还是支出，便于业务逻辑判断和页面展示

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