我正在尝试进行一个查询,该查询将返回两个表的左外部联接,但每次进行匹配时都需要额外一行。右边的多余行为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
)