SQL中外键替换参数实现佣金计算及C#前端DataGrid填充问询

一、修正SQL查询计算佣金 #

你之前的查询没正确关联Staff表，所以拿不到每个员工的佣金比例。我帮你调整了查询，把Staff表加入关联，用员工自己的Commission字段来计算佣金：

SELECT 
    s.StaffID,
    SUM(t.TreatmentPrice) AS TotalStaffRevenue,
    SUM(t.TreatmentPrice) * s.Commission AS StaffCommission
FROM 
    Treatment t
INNER JOIN 
    Appointment a ON a.TreatmentID = t.TreatmentID
INNER JOIN 
    Staff s ON a.StaffID = s.StaffID
WHERE 
    a.AppointmentDate >= '2018-05-11' 
    AND a.AppointmentDate <= '2018-05-12'
GROUP BY 
    s.StaffID, s.Commission WITH ROLLUP

改动说明： #

• 新增了和Staff表的INNER JOIN，通过Appointment的StaffID关联到Staff表的StaffID，这样就能获取每个员工的Commission值
• 把原来的?替换成s.Commission，确保每个员工用自己的佣金比例计算
• GROUP BY里加入了s.Commission，因为我们在SELECT里用到了这个字段（WITH ROLLUP需要GROUP BY包含所有非聚合字段）
• 给佣金列起了更清晰的别名StaffCommission

如果需要ROLLUP汇总所有员工的总营收和总佣金，这个查询会自动生成最后一行带NULL的汇总记录，你可以根据需要过滤或展示。

二、C#前端填充DataGrid的实现 #

方式1：自动数据绑定（推荐，无需foreach） #

这种方式最简洁，用ADO.NET直接把查询结果填充到DataSet，然后绑定到DataGrid，不需要手动遍历：

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms; // 适用于WinForms场景

// 假设是WinForms的DataGridView
private void LoadStaffCommissionData()
{
    string connectionString = "你的数据库连接字符串";
    string query = @"SELECT 
                        s.StaffID,
                        SUM(t.TreatmentPrice) AS TotalStaffRevenue,
                        SUM(t.TreatmentPrice) * s.Commission AS StaffCommission
                     FROM 
                        Treatment t
                     INNER JOIN 
                        Appointment a ON a.TreatmentID = t.TreatmentID
                     INNER JOIN 
                        Staff s ON a.StaffID = s.StaffID
                     WHERE 
                        a.AppointmentDate >= '2018-05-11' 
                        AND a.AppointmentDate <= '2018-05-12'
                     GROUP BY 
                        s.StaffID, s.Commission WITH ROLLUP";

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
        DataSet ds = new DataSet();
        adapter.Fill(ds, "StaffCommission");
        
        // 绑定到DataGridView
        dataGridView1.DataSource = ds.Tables["StaffCommission"];
    }
}

方式2：手动foreach循环（适合需要额外处理数据的场景） #

如果需要对每一行数据做自定义处理（比如格式化金额、添加额外字段），可以用foreach遍历DataTable的行，然后手动添加到DataGrid：

private void LoadStaffCommissionDataWithForeach()
{
    string connectionString = "你的数据库连接字符串";
    string query = @"SELECT 
                        s.StaffID,
                        SUM(t.TreatmentPrice) AS TotalStaffRevenue,
                        SUM(t.TreatmentPrice) * s.Commission AS StaffCommission
                     FROM 
                        Treatment t
                     INNER JOIN 
                        Appointment a ON a.TreatmentID = t.TreatmentID
                     INNER JOIN 
                        Staff s ON a.StaffID = s.StaffID
                     WHERE 
                        a.AppointmentDate >= '2018-05-11' 
                        AND a.AppointmentDate <= '2018-05-12'
                     GROUP BY 
                        s.StaffID, s.Commission WITH ROLLUP";

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(query, conn);
        SqlDataReader reader = cmd.ExecuteReader();
        
        // 先清空DataGrid的现有行
        dataGridView1.Rows.Clear();
        
        while (reader.Read())
        {
            // 读取每一行数据
            int staffId = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
            decimal totalRevenue = reader.IsDBNull(1) ? 0 : reader.GetDecimal(1);
            decimal commission = reader.IsDBNull(2) ? 0 : reader.GetDecimal(2);
            
            // 自定义处理：比如格式化金额为货币格式
            string formattedRevenue = totalRevenue.ToString("C");
            string formattedCommission = commission.ToString("C");
            
            // 添加到DataGrid
            dataGridView1.Rows.Add(staffId, formattedRevenue, formattedCommission);
        }
        
        reader.Close();
    }
}

如果你用的是WPF的DataGrid，逻辑类似，只是需要把数据放到ObservableCollection里，然后绑定DataGrid的ItemsSource属性，核心思路是一致的。

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