MySQL（phpMyAdmin）无聚合函数按日期实现数据透视需求

前提假设 #

假设你的薪资表（示例命名为courier_salary）结构如下（若实际结构不同可按需调整）：

CREATE TABLE courier_salary (
    id INT PRIMARY KEY AUTO_INCREMENT,
    courier_id INT,
    courier_name VARCHAR(50),
    salary DECIMAL(10,2),
    pay_date DATE
);

核心思路 #

要满足「无聚合函数、日期变量化」的要求，核心逻辑是：

1. 动态获取目标日期范围内的所有月份（或数据库中已存在的薪资月份）
2. 用CASE WHEN语句为每个月份生成对应列，直接匹配取出该月薪资（前提是每个快递员每月仅一条薪资记录）
3. 在CodeIgniter中通过动态拼接SQL实现透视效果

CodeIgniter 具体实现 #

步骤1：动态获取月份列表

基于传入的日期变量（比如前端传的起止日期），提取需要展示的月份：

// 从请求中获取日期变量，默认值可按需设置
$start_date = $this->input->get('start_date') ?? '2024-01-01';
$end_date = $this->input->get('end_date') ?? '2024-06-30';

// 查询该日期范围内的所有月份，格式化为`YYYY-MM`格式
$months = $this->db->select("DATE_FORMAT(pay_date, '%Y-%m') as month")
                   ->from('courier_salary')
                   ->where('pay_date >=', $start_date)
                   ->where('pay_date <=', $end_date)
                   ->group_by('month')
                   ->order_by('month')
                   ->get()
                   ->result_array();

// 提取纯月份字符串数组
$month_list = array_column($months, 'month');

步骤2：动态拼接透视列SQL

循环生成每个月份的CASE WHEN列，无需聚合函数直接取值：

// 初始化基础查询字段
$select_cols = [
    'courier_id',
    'courier_name'
];

// 循环添加每个月份的透视列
foreach ($month_list as $month) {
    // 匹配当前月份，取出对应薪资，无数据则显示NULL（可改为0）
    $col_sql = "CASE WHEN DATE_FORMAT(pay_date, '%Y-%m') = '$month' THEN salary ELSE NULL END AS `$month`";
    $select_cols[] = $col_sql;
}

// 构造并执行最终查询
$this->db->select(implode(', ', $select_cols))
         ->from('courier_salary')
         ->where('pay_date >=', $start_date)
         ->where('pay_date <=', $end_date)
         ->group_by('courier_id, courier_name'); // 按快递员分组，确保一行对应一个快递员

// 获取透视结果
$result = $this->db->get()->result_array();

步骤3：渲染目标格式

查询得到的$result结构如下，可直接在视图中渲染成表格：

[
    [
        'courier_id' => 1,
        'courier_name' => '张三',
        '2024-01' => 5000.00,
        '2024-02' => 5200.00,
        // ... 其他月份列
    ],
    // 其他快递员数据
]

关键说明 #

• 未使用MAX()/SUM()等聚合函数，依赖「每个快递员每月仅一条薪资记录」的业务前提，CASE WHEN可直接定位对应行的薪资值。
• 月份完全动态生成，依赖传入的日期变量或数据库实际存在的月份，无硬编码内容。
• 若需要无薪资月份显示0而非NULL，只需将ELSE NULL改为ELSE 0即可。

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