如何将MySQL表数据按月列形式加载到C# DataGridView中

1. MySQL数据透视查询（核心逻辑） #

要把纵向的月度记录转成横向列展示，用MySQL的条件聚合实现数据透视：

SELECT
    Empno,
    Empname,
    -- 按月份匹配并聚合应收款
    MAX(CASE WHEN MONTHNAME(Date) = 'January' THEN Receivable END) AS January,
    MAX(CASE WHEN MONTHNAME(Date) = 'February' THEN Receivable END) AS February,
    MAX(CASE WHEN MONTHNAME(Date) = 'March' THEN Receivable END) AS March,
    MAX(CASE WHEN MONTHNAME(Date) = 'April' THEN Receivable END) AS April,
    MAX(CASE WHEN MONTHNAME(Date) = 'May' THEN Receivable END) AS May,
    MAX(CASE WHEN MONTHNAME(Date) = 'June' THEN Receivable END) AS June,
    MAX(CASE WHEN MONTHNAME(Date) = 'July' THEN Receivable END) AS July,
    MAX(CASE WHEN MONTHNAME(Date) = 'August' THEN Receivable END) AS August,
    MAX(CASE WHEN MONTHNAME(Date) = 'September' THEN Receivable END) AS September,
    MAX(CASE WHEN MONTHNAME(Date) = 'October' THEN Receivable END) AS October,
    MAX(CASE WHEN MONTHNAME(Date) = 'November' THEN Receivable END) AS November,
    MAX(CASE WHEN MONTHNAME(Date) = 'December' THEN Receivable END) AS December,
    -- 如需同步展示对应月份状态，按相同逻辑扩展
    MAX(CASE WHEN MONTHNAME(Date) = 'January' THEN Status END) AS January_Status,
    MAX(CASE WHEN MONTHNAME(Date) = 'February' THEN Status END) AS February_Status
    -- 其他月份状态可按需添加
FROM
    your_table_name
-- 可选：过滤特定年份数据
-- WHERE YEAR(Date) = 2024
GROUP BY
    Empno, Empname;

说明：

• MONTHNAME(Date)用于提取日期的英文月份名称，匹配对应列
• MAX()聚合函数确保每个员工每个月仅返回一条记录，若同一员工同月有多条记录，可根据业务需求替换为SUM()（汇总应收款）或AVG()（平均值）
• 如需区分年份，可在GROUP BY和CASE条件中加入YEAR(Date)

2. C#加载数据到DataGridView #

使用MySqlConnector连接数据库，执行上述查询后绑定到DataGridView：

using MySqlConnector;
using System.Data;

// 替换为你的数据库连接配置
string connStr = "server=localhost;user=root;password=your_password;database=your_db;";
// 复制上述SQL查询语句
string pivotQuery = @"SELECT
    Empno,
    Empname,
    MAX(CASE WHEN MONTHNAME(Date) = 'January' THEN Receivable END) AS January,
    MAX(CASE WHEN MONTHNAME(Date) = 'February' THEN Receivable END) AS February,
    MAX(CASE WHEN MONTHNAME(Date) = 'March' THEN Receivable END) AS March,
    MAX(CASE WHEN MONTHNAME(Date) = 'April' THEN Receivable END) AS April,
    MAX(CASE WHEN MONTHNAME(Date) = 'May' THEN Receivable END) AS May,
    MAX(CASE WHEN MONTHNAME(Date) = 'June' THEN Receivable END) AS June,
    MAX(CASE WHEN MONTHNAME(Date) = 'July' THEN Receivable END) AS July,
    MAX(CASE WHEN MONTHNAME(Date) = 'August' THEN Receivable END) AS August,
    MAX(CASE WHEN MONTHNAME(Date) = 'September' THEN Receivable END) AS September,
    MAX(CASE WHEN MONTHNAME(Date) = 'October' THEN Receivable END) AS October,
    MAX(CASE WHEN MONTHNAME(Date) = 'November' THEN Receivable END) AS November,
    MAX(CASE WHEN MONTHNAME(Date) = 'December' THEN Receivable END) AS December
FROM
    your_table_name
GROUP BY
    Empno, Empname;";

DataTable resultTable = new DataTable();
using (MySqlConnection conn = new MySqlConnection(connStr))
{
    conn.Open();
    using (MySqlDataAdapter adapter = new MySqlDataAdapter(pivotQuery, conn))
    {
        adapter.Fill(resultTable);
    }
}

// 绑定到DataGridView
dataGridView1.DataSource = resultTable;
// 可自定义列标题（可选）
dataGridView1.Columns["Empno"].HeaderText = "员工编号";
dataGridView1.Columns["Empname"].HeaderText = "员工姓名";

3. 关键注意事项 #

• 提前通过NuGet安装MySqlConnector包（替代旧版MySql.Data）
• 若需动态适配数据中的年份和月份，可先查询所有存在的月份，再动态拼接SQL语句或在内存中处理DataTable
• 若同一员工同月有多条记录，务必选择符合业务需求的聚合函数

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