ASP.NET 4.0多表重复数据查询结果绑定GridView仅显示首条问题

问题背景 #

你开发了一个去重页面，需要从5个业务表（tbl_Subscribers、tbl_From_master、tbl_Categories、tbl_Template_Categories、tbl_Template_master）中提取所有重复值，虽然已经统一了查询的列名，但绑定GridView后页面只显示第一个表的查询结果。

问题原因 #

你最初的代码是直接把多个独立的SELECT语句拼接在一起，没有用合适的关键字合并结果集。数据库执行这类SQL时，只会处理第一个完整的查询语句，后续的查询会被直接忽略，导致GridView只能拿到第一个表的重复数据。要合并多个结构一致的查询结果，必须使用UNION ALL（或UNION）关键字。

解决方案 #

在每个独立的SELECT查询之间添加UNION ALL，同时要保证所有子查询返回的列数完全一致、对应列的数据类型兼容（如果某个表没有对应的字段，比如创建日期，可以用NULL填充占位）。

修正后的核心代码

if (groupByTable == 0) 
{ 
    str += "(SELECT "; 
    str += "s.Id, s.EmailAddress As ColumnValue,"; 
    str += "(SELECT Name FROM tbl_User_master WHERE Id = s.user_id) AS CreatedBy,"; 
    str += "s.SubscriptionDateTime As CreatedDate,d.Count "; 
    str += "FROM "; 
    str += "(SELECT EmailAddress, COUNT(*) as Count "; 
    str += "FROM tbl_Subscribers "; 
    str += "WHERE user_id = '" + int.Parse(CommonLogic.GetSessionValue("user_id").ToString()) + "' "; 
    str += "GROUP BY EmailAddress "; 
    str += "HAVING COUNT(*) > 1) AS d "; 
    str += "INNER JOIN "; 
    str += "tbl_Subscribers s ON s.EmailAddress = d.EmailAddress)"; 
    str += " UNION ALL "; 
    str += "(SELECT "; 
    str += "f.Id,f.EmailAddress As ColumnValue,"; 
    str += "(SELECT Name FROM tbl_User_master WHERE Id = f.user_id) AS CreatedBy,"; 
    str += "f.CreatedDate As CreatedDate,d.Count "; 
    str += "FROM "; 
    str += "(SELECT EmailAddress, COUNT(*) AS Count "; 
    str += "FROM tbl_From_master "; 
    str += "WHERE user_id = '" + int.Parse(CommonLogic.GetSessionValue("user_id").ToString()) + "' "; 
    str += "GROUP BY EmailAddress "; 
    str += "HAVING COUNT(*) > 1) AS d "; 
    str += "INNER JOIN "; 
    str += "tbl_From_master f ON f.EmailAddress = d.EmailAddress)"; 
    str += " UNION ALL "; 
    str += "(SELECT "; 
    str += "c.Id,c.CategoryName As ColumnValue,(SELECT Name FROM tbl_User_master WHERE Id = c.user_id) As CreatedBy,NULL As CreatedDate,d.Count "; 
    str += "FROM ("; 
    str += "SELECT CategoryName, COUNT(*) as Count "; 
    str += "FROM tbl_Categories WHERE user_id='" + int.Parse(CommonLogic.GetSessionValue("user_id").ToString()) + "' "; 
    str += "GROUP BY CategoryName "; 
    str += "HAVING COUNT(*) > 1"; 
    str += ") AS d "; 
    str += "INNER JOIN tbl_Categories c ON c.CategoryName = d.CategoryName)"; 
    str += " UNION ALL "; 
    str += "(SELECT "; 
    str += "t.Id,t.CategoryName As ColumnValue,(SELECT Name FROM tbl_User_master WHERE Id = t.user_id) As CreatedBy,NULL As CreatedDate,d.Count "; 
    str += "FROM ("; 
    str += "SELECT CategoryName, COUNT(*) as Count "; 
    str += "FROM tbl_Template_Categories WHERE user_id='" + int.Parse(CommonLogic.GetSessionValue("user_id").ToString()) + "' "; 
    str += "GROUP BY CategoryName "; 
    str += "HAVING COUNT(*) > 1"; 
    str += ") AS d "; 
    str += "INNER JOIN tbl_Template_Categories t ON t.CategoryName = d.CategoryName)"; 
    str += " UNION ALL "; 
    str += "(SELECT "; 
    str += "t.Id,t.TemplateName As ColumnValue,(SELECT Name FROM tbl_User_master WHERE Id = t.user_id) As CreatedBy,t.Created_date As CreatedDate,d.Count "; 
    str += "FROM ("; 
    str += "SELECT TemplateName, COUNT(*) as Count "; 
    str += "FROM tbl_Template_master WHERE user_id='" + int.Parse(CommonLogic.GetSessionValue("user_id").ToString()) + "' "; 
    str += "GROUP BY TemplateName "; 
    str += "HAVING COUNT(*) > 1"; 
    str += ") AS d"; 
    str += " INNER JOIN tbl_Template_master t ON t.TemplateName = d.TemplateName)"; 
}

// 后续数据绑定逻辑保持不变
dt = obj.Get_Data_Table_From_Str(str); 
egrd.DataSource = dt; 
egrd.DataBind(); 
ViewState["data"] = dt;

额外注意事项 #

1. UNION ALL的优势：相比UNION，UNION ALL不会自动去重，能完整保留所有重复记录（包括不同表中的重复项），且性能更优，完全匹配你的去重页面需求。
2. 列一致性要求：所有子查询必须返回相同数量的列，且对应列的数据类型要兼容。比如tbl_Categories没有创建日期字段，就用NULL As CreatedDate占位，确保结果集结构统一。
3. SQL注入风险：当前代码直接拼接user_id存在SQL注入隐患，建议改用参数化查询，比如通过SqlCommand的Parameters集合传递参数，提升代码安全性。

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