我正在尝试进行一个查询,该查询将返回两个表的左外部联接,但每次进行匹配时都需要额外一行。右边的多余行为null。这是一个例子:



这是我当前使用linq的查询(对SQL的响应也表示赞赏):

context.Code
       .GroupJoin(
           context.SubCode,
           code => code.CodeID,
           sub => sub.CodeID,
           (code, sub) => new
           {
               Codes = code,
               SubCodes = sub
           })
       .SelectMany(sub => sub.SubCodes.DefaultIfEmpty(),
           (code, sub) => new { Codes = code.Codes, SubCodes = sub })
       .ToList();


编辑(解决方案?):

SELECT Codes.ID, Codes.Code, SubCodes.SubCode
FROM Codes
INNER JOIN SubCodes
ON Codes.ID = SubCodes.CodeID
UNION
SELECT Codes.ID, Codes.Code, NULL
FROM Codes
ORDER BY Codes.ID

最佳答案

这是SQL,可以满足您的要求:

(select c.id, c.code, sc.subCode
 from codes c join
      subcodes sc
      on c.id = sc.codeid
) union all
(select c.id, c.code, NULL
 from codes c
)

10-08 14:49