問題描述

我正在嘗試將 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.

這篇關于如果 join 返回 null，則在默認行上加入的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！