C#应用中如何统计MySQL表近14天的gold字段平均值？

核心错误修正 #

你之前的WHERE条件存在两个关键问题：

1. 逻辑方向错误：timestamp <= NOW() + INTERVAL 14 DAY是筛选未来14天的数据，而你需要的是**近14天（过去14天）**的数据，应改为timestamp >= NOW() - INTERVAL 14 DAY
2. 位置错误：WHERE子句必须放在FROM items之后、GROUP BY之前，不能直接追加在语句末尾

修改后的完整代码 #

同时建议改用参数化查询替代字符串拼接，避免SQL注入风险，以下是优化后的代码：

private DataTable GetAverageGold()
{
    var dtAverageGold = new DataTable();
    var percentageBuy = double.TryParse(textBox1.Text.Replace("%", string.Empty), out var sell) ? sell / 100 : 0.02;

    using (_con = GetMySqlConnection())
    {
        // 修正WHERE条件位置和逻辑，同时改用参数化查询
        var sql = @"SELECT 
                        name AS name, 
                        FORMAT(GROUP_CONCAT(gold ORDER BY id DESC), 'de_DE') AS 'MOST RECENT', 
                        FORMAT(ROUND(AVG(gold) - AVG(gold) * @percentageBuy), 'de_DE') AS Einkaufspreis, 
                        FORMAT(ROUND(AVG(gold)),'de_DE') AS Durchschnitt, 
                        ROUND((AVG(gold) - SUBSTRING_INDEX(GROUP_CONCAT(gold ORDER BY id DESC), ',', 1)) / SUBSTRING_INDEX(GROUP_CONCAT(gold ORDER BY id DESC), ',', 1) * 100,2)  AS 'profit in %' 
                    FROM items 
                    WHERE timestamp >= NOW() - INTERVAL 14 DAY  -- 筛选近14天数据
                    GROUP BY name 
                    ORDER BY name";

        using (var cmd = new MySqlCommand(sql, _con))
        {
            // 添加参数，避免SQL注入
            cmd.Parameters.AddWithValue("@percentageBuy", percentageBuy);

            try
            {
                _con.Open();
                var reader = cmd.ExecuteReader(); 
                dtAverageGold.Load(reader);
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.ToString(), "Error in: GetAverageGold", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                _con.Close();
            }
        }
    }

    return dtAverageGold;
}

额外优化说明 #

原SQL中直接使用GROUP_CONCAT(gold ORDER BY id DESC)参与数值计算会出错（因为GROUP_CONCAT返回的是逗号分隔的字符串），所以改用SUBSTRING_INDEX(GROUP_CONCAT(gold ORDER BY id DESC), ',', 1)提取最新的单条gold值，确保利润百分比计算逻辑正确。

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