sql-server - 在 SQL Server 中强制部分连接顺序

Question

编辑:人们很难理解我想要什么。所以这里有一些漂亮的图片，详细地解释了它。

首先将交易加入到奇怪:

目前的结果

Customer  Invoice  TransactionID  Mass  Length                LeptonNumber
========  =======  =============  ====  ====================  ============
Ian       One      1              Ian   Judgement Spaulders   50
Ian       One      1              Ian   Glorious Breastplate  50
Chris     Two      2              Chris Barenavel             2

现在尝试使用 Down 连接剩余行:

目前的结果

Customer  Invoice         TransactionID  Mass  Length                LeptonNumber
========  =======         =============  ====  ====================  ============
Ian       One             1              Ian   Judgement Spaulders   50
Ian       One             1              Ian   Glorious Breastplate  50
Chris     Two             2              Chris Barenavel             2
Jamie     Krol Blade      3              Jay   Krol Blade            90
Jay       Arcanite Reaper 4              Ian   Arcanite Reaper       90

最后，将剩余的行加入到 Charmed 中:

目前的结果

Customer  Invoice         TransactionID  Mass    Length                LeptonNumber
========  =======         =============  ====    ====================  ============
Ian       One             1              Ian     Judgement Spaulders   50
Ian       One             1              Ian     Glorious Breastplate  50
Chris     Two             2              Chris   Barenavel             2
Jamie     Krol Blade      3              Jay     Krol Blade            90
Jay       Arcanite Reaper 4              Ian     Arcanite Reaper       90
Potatoe   Dan Quayle      5              Potatoe Dan Quayle            90

以及如何查看我们剩下的行:

给我我想要的结果集

Customer  Invoice         TransactionID  Mass    Length                LeptonNumber
========  =======         =============  ====    ====================  ============
Ian       One             1              Ian     Judgement Spaulders   50
Ian       One             1              Ian     Glorious Breastplate  50
Chris     Two             2              Chris   Barenavel             2
Jamie     Krol Blade      3              Jay     Krol Blade            90
Jay       Arcanite Reaper 4              Ian     Arcanite Reaper       90
Potatoe   Dan Quayle      5              Potatoe Dan Quayle            90
Stapler   Alexstraza      6              NULL    NULL                  NULL

<小时/>

我有一个主表:

Transactions
+----------+
|          |
|          | 
|          |
|          |
|          |
|          |
|          |
+----------+

我希望此表中的每一行仅连接一个可能的匹配表:

Tranasctions        Strange
+----------+      +----------+
| row 1 ===|=====>| row 1    |         Down
| row 2 ===|=====>| row 2    |       +---------+
| row 3 ===|======+----------+======>| row 1   |        Charmed
| row 4 ===|========================>| row 2   |       +---------+
| row 5 ===|=========================+---------+======>| row 1   |
| row 6 ===|==========================================>| row 2   |
+----------+                                           +---------+

通常我会将Transactions 与Strange || 集合的连接来执行。向下||着迷:

SELECT
   Transactions.*,
   Quarks.Mass,
   Quarks.Length,
   Quarks.LeptonNumber
FROM Transactions
    INNER JOIN NationalSecurityLetters
    ON Transactions.TransactionID = NationalSecurityLetters.ReferenceNumber

    LEFT JOIN (
       SELECT 'Strange' AS Type, * FROM Strange
       UNION ALL
       SELECT 'Down' AS Type, * FROM Down
       UNION ALL
       SELECT 'Charmed' AS Type, * FROM Charmed
    ) Quarks
    ON (
        (Quarks.Type = 'Strange' AND Transactions.Customer = Quarks.Mass)
        OR
        (Quarks.Type = 'Down' AND Transactions.Invoice = Quarks.Length)
        OR
        (Quarks.Type = 'Charmed' AND Transactions.Customer = Quarks.Length)    
    )       

问题是我希望加入按首选顺序发生:

• 奇怪
• 向下
• 着迷

单个事务完全有可能在多个表中具有匹配的条目。但对于每一个可能的事务与其他表的JOIN，我希望SQL Server更喜欢Strange表。如果没有匹配项，则转到Down 表。如果没有匹配项，请前往 Charmed 表。

If you find a match in      Prefer the matching row from
==========================  ============================
Strange                     Strange
Strange and Down            Strange
Strange, Down, and Charmed  Strange
Down                        Down
Down and Charmed            Down
Charmed                     Charmed
(no match?)                 (then there's no match)

我考虑过使用OPTION(FORCE ORDER)子句:

SELECT *
FROM Transactions
    INNER JOIN NationalSecurityLetters
    ON Transactions.TransactionID = NationalSecurityLetters.ReferenceNumber

    LEFT JOIN (
       SELECT 'Strange' AS Type, * FROM Strange
       UNION ALL
       SELECT 'Down' AS Type, * FROM Strange
       UNION ALL
       SELECT 'Charmed' AS Type, * FROM Strange
    ) Quarks
    ON (
        (Quarks.Type = 'Strange' AND Transactions.Customer = Quarks.Mass)
        OR
        (Quarks.Type = 'Down' AND Transactions.Invoice = Quarks.Length)
        OR
        (Quarks.Type = 'Charmed' AND Transactions.Customer = Quarks.Length)    
    )       
OPTION (FORCE ORDER)

但我不想强制 SQL Server 加入

• 交易 ==> NationalSecurityLetters，当加入可能更有利时
• 国家安全信件 ==> 交易

<小时/>

Answer 1

正如 @AaronBertrand 提到的，我有点不清楚你想要做什么，但如果你正在谈论改变你的输出，你可以使用 COALESCE 吗？示例:

SELECT COALESCE(s.Value, d.Value, c.Value), t.*
FROM Transactions as t
LEFT JOIN Strange as s
ON t.id = s.tid
LEFT JOIN Down as d
ON t.id = d.tid
LEFT JOIN Charmed as c
ON t.id = c.tid