如何将多个SQL查询语句合并为单个查询并优化代码逻辑

需求 #

将现有代码中的多个SQL查询合并为单个查询，整合所有SQL逻辑，实现父分类加粗、子分类缩进的层级输出效果。原代码包含三个独立查询：父分类查询、总条数统计查询、循环内的子分类逐个查询。

原代码 #

string sql = "SELECT DISTINCT([Name]), Id FROM Kategori WHERE id IN ('3025', '3003', '3006', '3004') ORDER BY [Name] DESC";

DataTable dth = conn.GetDataTable(sql);

string sqlcount = "SELECT DISTINCT([Name]), Id FROM Kategori WHERE id IN ('3025', '3003', '3006', '3004') OR ParentKategori_Id IN ('3025', '3003', '3006', '3004') ORDER BY [Name] DESC";

DataTable rc = conn.GetDataTable(sqlcount);

int rowhasil = rc.Rows.Count + row;

foreach (DataRow dr in dth.Rows)
{
    worksheet.Cell(row, 1).Value = dr["Name"].ToString();
    worksheet.Cell(row, 1).Style.Font.Bold = true;
    row++;

    string query = "SELECT [Name], [id] FROM Kategori WHERE [TYPE] = '2' AND ParentKategori_Id = " + dr["Id"].ToString() + "";

    DataTable dtd = conn.GetDataTable(query);

    foreach (DataRow drd in dtd.Rows)
    {
        worksheet.Cell(row, 1).Value = "      " + drd["Name"].ToString();
        row++;
    }
}

优化方案 #

使用CTE（公共表表达式）一次性拉取所有层级数据，减少数据库交互，同时优化总条数计算逻辑：

// 单个整合SQL，用CTE关联父、子分类数据
string combinedSql = @"
WITH ParentCategories AS (
    SELECT DISTINCT [Name] AS ParentName, Id AS ParentId 
    FROM Kategori 
    WHERE id IN ('3025', '3003', '3006', '3004')
),
ChildCategories AS (
    SELECT k.[Name] AS ChildName, k.ParentKategori_Id
    FROM Kategori k
    JOIN ParentCategories pc ON k.ParentKategori_Id = pc.ParentId
    WHERE k.[TYPE] = '2'
)
SELECT 
    pc.ParentName, pc.ParentId,
    cc.ChildName
FROM ParentCategories pc
LEFT JOIN ChildCategories cc ON pc.ParentId = cc.ParentKategori_Id
ORDER BY pc.ParentName DESC";

// 执行单次查询获取全量数据
DataTable dt = conn.GetDataTable(combinedSql);

// 计算总条数：父分类数 + 子分类数
int parentCount = dt.AsEnumerable().Select(r => r["ParentId"]).Distinct().Count();
int childCount = dt.AsEnumerable().Where(r => r["ChildName"] != DBNull.Value).Count();
int rowhasil = parentCount + childCount + row;

// 处理层级输出
string currentParent = null;
foreach (DataRow dr in dt.Rows)
{
    string parentName = dr["ParentName"].ToString();
    if (parentName != currentParent)
    {
        // 输出父分类并设置加粗
        worksheet.Cell(row, 1).Value = parentName;
        worksheet.Cell(row, 1).Style.Font.Bold = true;
        row++;
        currentParent = parentName;
    }

    // 输出子分类（存在则显示）
    if (dr["ChildName"] != DBNull.Value)
    {
        worksheet.Cell(row, 1).Value = "      " + dr["ChildName"].ToString();
        row++;
    }
}

关键优化点 #

1. 减少数据库交互：从原有的N+1次查询（1次父分类+N次子分类）简化为1次查询，提升性能
2. 整合统计逻辑：通过Linq对结果集直接统计父/子分类数量，替代独立的统计查询
3. 层级逻辑清晰：用CTE明确关联父、子分类关系，SQL可读性更强
4. 安全建议：如果分类ID是动态传入的，建议改用SqlParameter实现参数化查询，避免SQL注入风险

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