MySQL查询初始余额返回异常 求问题排查与解决

核心问题分析 #

1. 查询范围过滤了期初数据
   你当前的查询通过billed_date BETWEEN '$start' AND '$end'只筛选了起止日期内的记录，但计算期初余额需要的是$start之前的所有收发记录，这部分数据被WHERE条件直接排除了，所以p2和balance自然返回0。

2. CASE语句语法错误

   • 你使用的CASE 字段 WHEN 多条件写法不成立，正确的多条件CASE应该写成CASE WHEN 条件1 THEN ... WHEN 条件2 THEN ... END的形式，而不是绑定字段后用AND连接条件。
   • balance字段里的store_update_stock_details.qty AND store_update_stock.billed_date <= "$start"是逻辑错误，AND在这里会返回布尔值（0或1），不是你需要的数量值。

3. 数量计算逻辑混淆
   出库数量应该是负数，入库是正数，但你当前的quantity字段不管条件都返回qty * (-1)，逻辑有误。

修正后的Model代码 #

public function issueDetailReport($id, $start, $end) {
    // 先计算期初余额：$start之前的所有入库减出库
    $initial_balance_query = $this->db->select('
        SUM(CASE 
            WHEN order_status = "purchase" THEN qty 
            WHEN order_status = "issue" THEN -qty 
            ELSE 0 
        END) as initial_balance
    ')
    ->from('store_update_stock')
    ->join('store_update_stock_details', 'store_update_stock.update_stock_id=store_update_stock_details.update_stock_id', 'inner')
    ->where('store_update_stock.status=1')
    ->where('store_update_stock_details.item', $id)
    ->where('store_update_stock.billed_date <', $start)
    ->get();
    
    $initial_balance = $initial_balance_query->row()->initial_balance ?? 0;

    // 查询起止日期内的收发记录
    $this->db->select('
        *,
        (CASE 
            WHEN store_update_stock.order_status = "issue" THEN store_branch.branch_name     
            ELSE tbl_supplier.supplier_name
        END) supplier_officer_name,
        (CASE 
            WHEN store_update_stock.order_status = "issue" THEN store_update_stock.request_no      
            ELSE store_update_stock.bill_no
        END) number,
        (CASE 
            WHEN store_update_stock.order_status = "issue" THEN -store_update_stock_details.qty
            ELSE store_update_stock_details.qty
        END) quantity,
        -- 当期入库数量
        (CASE WHEN store_update_stock.order_status = "purchase" THEN store_update_stock_details.qty ELSE 0 END) current_purchase,
        -- 当期出库数量
        (CASE WHEN store_update_stock.order_status = "issue" THEN store_update_stock_details.qty ELSE 0 END) current_issue
    ');
    $this->db->from('store_update_stock');
    $this->db->join('store_update_stock_details', 'store_update_stock.update_stock_id=store_update_stock_details.update_stock_id', 'inner');
    $this->db->join('store_branch', 'store_update_stock.branch_id=store_branch.branch_id', 'left');
    $this->db->join('tbl_supplier', 'store_update_stock.supplier=tbl_supplier.supplier_id', 'left');
    $this->db->join('store_item', 'store_update_stock_details.item=store_item.item_id', 'left');
    $this->db->where('store_update_stock.status=1');
    $this->db->where('store_item.item_id', $id);
    $this->db->where('store_update_stock_details.qty <> 0');
    if ($start != NULL && $end != NULL) {
        $this->db->where('store_update_stock.billed_date BETWEEN', $start, $end);
    }
    $this->db->order_by('store_update_stock.billed_date', 'ASC');
    $q = $this->db->get();

    if ($q->num_rows() > 0) {
        $result = $q->result();
        // 把期初余额添加到结果中
        foreach ($result as $row) {
            $row->initial_balance = $initial_balance;
            // 计算当前累计余额：期初 + 当期入库 - 当期出库
            $row->current_balance = $initial_balance + ($row->current_purchase ?? 0) - ($row->current_issue ?? 0);
        }
        return $result;
    }
    // 如果没有当期记录，返回期初余额
    return (object)[
        'initial_balance' => $initial_balance,
        'current_purchase' => 0,
        'current_issue' => 0,
        'current_balance' => $initial_balance
    ];
}

关键修改说明 #

• 拆分期初余额计算：单独查询$start之前的所有收发记录，计算出期初余额，避免和当期记录的查询范围冲突。
• 修正CASE语句语法：改用CASE WHEN 条件 THEN ...的正确写法，明确区分入库和出库的数量正负。
• 分离当期和期初数据：避免在同一个查询里混合不同时间范围的数据，逻辑更清晰。
• 添加累计余额计算：在结果中直接返回当期每一条记录对应的实时余额。

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