C#如何实现两个List<(string,string,string)>按首元素类SQL全连接比对

实现思路 #

• 先将两个列表分别转换为以元组第一个元素为键的字典，提升查找效率
• 提取两个列表所有键的并集，遍历每个键做匹配判断
• 键在两个列表都存在时输出两侧元组，仅单侧存在时另一侧输出NULL标识

示例代码 #

// 两个待比对的三元组列表，元组第一个元素为匹配键
List<(string key, string val1, string val2)> list1 = new List<(string, string, string)>
{
    ("123", "4", "1"),
    ("23", "4", "1"),
    ("77", "4", "1")
};
List<(string key, string val1, string val2)> list2 = new List<(string, string, string)>
{
    ("123", "a", "b"),
    ("23", "a", "b"),
    ("423", "a", "b")
};

// 转换为字典，键为匹配键，值为对应元组
var dict1 = list1.ToDictionary(x => x.key, x => x);
var dict2 = list2.ToDictionary(x => x.key, x => x);

// 取两个列表所有匹配键的并集
var allKeys = dict1.Keys.Union(dict2.Keys).ToList();

// 遍历输出全连接结果
foreach (var key in allKeys)
{
    bool existIn1 = dict1.TryGetValue(key, out var item1);
    bool existIn2 = dict2.TryGetValue(key, out var item2);
    
    string leftContent = existIn1 ? $"(\"{item1.key}\",\"{item1.val1}\",\"{item1.val2}\")" : "NULL";
    string rightContent = existIn2 ? $"(\"{item2.key}\",\"{item2.val1}\",\"{item2.val2}\")" : "NULL";
    
    // 左侧补空格对齐输出
    Console.WriteLine($"{leftContent,-16} and {rightContent}");
}

输出效果 #

("123","4","1")  and ("123","a","b")
("23","4","1")   and ("23","a","b")
("77","4","1")   and NULL
NULL             and ("423","a","b")

注意事项 #

如果单个列表中存在多个相同匹配键的元组，可将ToDictionary替换为ToLookup做分组处理，遍历每个键下的所有元组组合输出即可。

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