ASP.NET C#中求两个SQL查询结果的占比问题求助

一、你的SQL语句存在的问题 #

1. JOIN条件不完整：仅通过shop_number关联两张表，未匹配distict字段，会导致不同地区下编号相同的店铺错误关联，返回错误的比值。
2. INNER JOIN过滤了无达标员工的店铺：如果某个店铺没有score >=80的员工，INNER JOIN会直接排除该店铺的记录，无法展示这类店铺的占比（应为0）。
3. 整数除法导致结果失真：SQL中整数相除会自动取整（比如3/5结果为0），无法得到精确的小数占比。
4. （可选）字段拼写疑问：distict疑似拼写错误，正确应为district，如果数据库字段确实是distict可忽略此点。

二、修正后的SQL语句 #

使用LEFT JOIN关联完整的分组字段，同时转换数值类型避免整数除法，处理NULL值：

select 
    a.distict, 
    a.shop_number, 
    -- 转换为浮点型计算，无达标员工时设为0
    ISNULL(CAST(b.number AS FLOAT)/a.number, 0) AS pass_rate
from 
    (select distict, shop_number, count(Emp_id) as number 
     from Shops_and_Employee_Table 
     group by distict, shop_number) a
left join 
    (select distict, shop_number, count(Emp_id) as number 
     from Shops_and_Employee_Table 
     where score >= 80 
     group by distict, shop_number) b 
on a.distict = b.distict AND a.shop_number = b.shop_number

三、C#后端计算的实现方法 #

如果更倾向于在后端处理，可以先获取前两个查询的结果集，再通过关联计算比值：

1. 使用DataTable处理的示例：

// 获取总员工数的DataTable（自行实现执行第一个SQL的方法）
DataTable totalDt = GetTotalEmployeeData();
// 获取达标员工数的DataTable（自行实现执行第二个SQL的方法）
DataTable passDt = GetPassEmployeeData();

// 创建用于绑定的结果DataTable
DataTable rateDt = new DataTable();
rateDt.Columns.Add("distict", typeof(string));
rateDt.Columns.Add("shop_number", typeof(string));
rateDt.Columns.Add("pass_rate", typeof(decimal));

// 遍历总员工数表，匹配达标数据计算比值
foreach (DataRow totalRow in totalDt.Rows)
{
    string district = totalRow["distict"].ToString();
    string shopNum = totalRow["shop_number"].ToString();
    int totalCount = Convert.ToInt32(totalRow["count(Emp_id)"]);
    
    // 查找对应店铺的达标数
    DataRow[] passRows = passDt.Select($"distict = '{district}' AND shop_number = '{shopNum}'");
    int passCount = passRows.Length > 0 ? Convert.ToInt32(passRows[0]["count(Emp_id)"]) : 0;
    
    // 计算占比（避免除以0）
    decimal rate = totalCount == 0 ? 0 : (decimal)passCount / totalCount;
    
    // 添加到结果表
    rateDt.Rows.Add(district, shopNum, rate);
}

// 绑定到第三个GridView
gvPassRate.DataSource = rateDt;
gvPassRate.DataBind();

2. 使用LINQ处理实体类的示例（假设已将数据映射为ShopEmployeeStats实体）：

List<ShopEmployeeStats> totalStats = GetTotalEmployeeStats();
List<ShopEmployeeStats> passStats = GetPassEmployeeStats();

var rateStats = from total in totalStats
                join pass in passStats 
                on new { total.Distict, total.ShopNumber } equals new { pass.Distict, pass.ShopNumber } into passGroup
                from pass in passGroup.DefaultIfEmpty()
                select new 
                {
                    total.Distict,
                    total.ShopNumber,
                    PassRate = total.TotalCount == 0 ? 0 : (decimal)(pass?.PassCount ?? 0) / total.TotalCount
                };

gvPassRate.DataSource = rateStats.ToList();
gvPassRate.DataBind();

内容的提问来源于stack exchange，提问作者Hussain Mahdi Farhanian