如果 join 返回 null，则在默认行上加入

问题描述

我正在尝试将 2 个表连接到一行，如果该行为空，则将该行连接到默认行.

I am trying to join 2 tables on a row, and if that row is null, then join that row to the default row.

表 1:事件

EventID     EventName
----------- -------------
1           January
2           February
3           March
4           April

表 2:菜单

MenuID     EventID     MenuVersion     
---------- ----------- --------------- 
1                      1
2          3           2
3          4           4
4                      4

我的尝试

SELECT * FROM Events
LEFT JOIN Menus
ON Events.EventID = Menus.EventID

我得到的输出

EventID     EventName     MenuID    EventID    MenuVersion
----------- ------------- --------- ---------- ---------------
1           January                           
2           February                          
3           March         2         3          2
4           April         3         4          4

在这种情况下，Menus 表的默认行是具有最高 MenuID 和空 EventID 的行.

The default row of the Menus table in this case is the row with the highest MenuID and a null EventID.

我想要的输出

EventID     EventName     MenuID    EventID    MenuVersion
----------- ------------- --------- ---------- ---------------
1           January       4                    4
2           February      4                    4
3           March         2         3          2
4           April         3         4          4

推荐答案

交叉应用默认行，并在没有行被left join打开时使用其值.

Cross apply the default row and use its values when no row is left joined on.

DECLARE @Events TABLE (EventId INT, EventName VARCHAR(12));
DECLARE @Menus TABLE (MenuId INT, EventId INT, MenuVersion INT);

INSERT INTO @Events (EventId, EventName)
VALUES
(1, 'January'),
(2, 'February'),
(3, 'March'),
(4, 'April');

INSERT INTO @Menus (MenuId, EventId, MenuVersion)
VALUES
(1, null, 1),
(2, 3, 2),
(3, 4, 4),
(4, null, 4);

SELECT E.EventId, E.EventName, COALESCE(M.MenuId, D.MenuId) MenuId, M.EventId, COALESCE(M.MenuVersion, D.MenuVersion) MenuVersion
FROM @Events E
LEFT JOIN @Menus M ON M.EventID = E.EventID
CROSS APPLY (SELECT TOP 1 * FROM @Menus WHERE EventId IS NULL ORDER BY MenuId DESC) D;

按要求返回:

EventId EventName   MenuId  EventId MenuVersion
1       January     4       NULL    4
2       February    4       NULL    4
3       March       2       3       2
4       April       3       4       4

注意:如果您将来使用 DDL/DML 语句提出这样的问题，您会得到更快的答复，因为它使人们不必全部输入.

Note: If you set out your questions like this in future with the DDL/DML statements you'll get a much faster response because it saves people from having to type it all in.