sql - 如果 join 返回 null 则加入默认行

Question

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

表 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和 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

标签： sqlsql-servertsqljoinleft-join

Answer 1

Cross apply默认行并在没有行被left join编辑时使用其值。

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 语句提出这样的问题，您将获得更快的响应，因为它使人们不必全部输入。