如何用SQL或LINQ按逗号分隔键关联并汇总数据表数据？

嘿，这个需求完全可以用SQL或者LINQ来搞定，而且效率绝对比你之前的C#实现高一大截！我给你分两种方案详细说说，你可以根据自己的数据库类型和项目情况选：

核心思路是把InfoTable中逗号分隔的AVNRString拆成单个AVNR值，再关联DataTable做分组聚合——这种数据库层面的操作比C#循环查询快得多，因为数据库对关联、聚合逻辑有专门的优化。

针对SQL Server的实现 #

SQL Server自带STRING_SPLIT函数，直接拆分字符串很方便：

SELECT 
    it.Substation,
    it.ColumnTitle,
    it.S6_name,
    dt.Pdate,
    dt.Ptime,
    SUM(dt.Wert) AS TotalWert
FROM InfoTable it
-- 把每个InfoTable行的AVNRString拆成多行单个AVNR
CROSS APPLY STRING_SPLIT(it.AVNRString, ',') AS avnr
-- 关联DataTable匹配AVNR
JOIN DataTable dt ON dt.AVNR = avnr.value
-- 按要求的维度分组
GROUP BY 
    it.Substation,
    it.ColumnTitle,
    it.S6_name,
    dt.Pdate,
    dt.Ptime

针对MySQL的实现 #

MySQL没有内置的拆分函数，用递归CTE来拆分逗号分隔的字符串：

WITH RECURSIVE SplitAVNR AS (
    -- 初始化：拆分第一个AVNR
    SELECT 
        Substation,
        ColumnTitle,
        S6_name,
        SUBSTRING_INDEX(AVNRString, ',', 1) AS AVNR,
        SUBSTRING(AVNRString, LENGTH(SUBSTRING_INDEX(AVNRString, ',', 1)) + 2) AS Remaining
    FROM InfoTable
    WHERE AVNRString != ''
    UNION ALL
    -- 递归拆分剩余的AVNR
    SELECT 
        Substation,
        ColumnTitle,
        S6_name,
        SUBSTRING_INDEX(Remaining, ',', 1) AS AVNR,
        SUBSTRING(Remaining, LENGTH(SUBSTRING_INDEX(Remaining, ',', 1)) + 2) AS Remaining
    FROM SplitAVNR
    WHERE Remaining != ''
)
-- 关联DataTable并分组求和
SELECT 
    sa.Substation,
    sa.ColumnTitle,
    sa.S6_name,
    dt.Pdate,
    dt.Ptime,
    SUM(dt.Wert) AS TotalWert
FROM SplitAVNR sa
JOIN DataTable dt ON dt.AVNR = sa.AVNR
GROUP BY 
    sa.Substation,
    sa.ColumnTitle,
    sa.S6_name,
    dt.Pdate,
    dt.Ptime;

如果想用C#的LINQ来做，分两种场景：数据库端执行（推荐）和内存中执行（适合小数据量）。

数据库端执行（EF/LINQ to SQL） #

这种方式会让LINQ自动转换成类似上面的SQL语句，在数据库端执行，效率和直接写SQL差不多：

// 假设db是你的DbContext实例
var result = from it in db.InfoTable
             // 拆分AVNRString（EF Core 3+支持用EF.Functions.SplitString适配SQL Server）
             from avnr in EF.Functions.SplitString(it.AVNRString, ",")
             // 关联DataTable
             join dt in db.DataTable on avnr equals dt.AVNR
             // 按要求维度分组
             group new { it, dt } by new {
                 it.Substation,
                 it.ColumnTitle,
                 it.S6_name,
                 dt.Pdate,
                 dt.Ptime
             } into g
             // 汇总结果
             select new {
                 g.Key.Substation,
                 g.Key.ColumnTitle,
                 g.Key.S6_name,
                 g.Key.Pdate,
                 g.Key.Ptime,
                 TotalWert = g.Sum(x => x.dt.Wert)
             };

内存中执行（数据已加载到本地） #

如果数据已经从数据库拉到内存列表里，可以用Lookup来优化关联速度：

// 先把DataTable转成按AVNR分组的Lookup，提升查询效率
var avnrWertLookup = dataList.ToLookup(dt => dt.AVNR, dt => dt);

var result = from it in infoList
             // 拆分每个InfoTable的AVNRString
             from avnr in it.AVNRString.Split(',')
             // 匹配对应的DataTable记录
             from dt in avnrWertLookup[avnr]
             // 按要求维度分组
             group dt by new {
                 it.Substation,
                 it.ColumnTitle,
                 it.S6_name,
                 dt.Pdate,
                 dt.Ptime
             } into g
             // 汇总求和
             select new {
                 g.Key.Substation,
                 g.Key.ColumnTitle,
                 g.Key.S6_name,
                 g.Key.Pdate,
                 g.Key.Ptime,
                 TotalWert = g.Sum(x => x.Wert)
             };

1. 给DataTable的AVNR字段加索引，加速关联匹配；
2. 给InfoTable的Substation, ColumnTitle, S6_name加组合索引，提升分组效率；
3. 绝对避免原来的“循环每条InfoTable记录，单独查询DataTable”的N+1模式，这是性能差的核心原因。

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