在SQL Server中强制执行部分连接顺序 [英] Force partial join order in SQL Server
问题描述
编辑:人们很难理解我想要的内容.因此，这里有漂亮的图片，详细地解释了这一点.
Edit: People are having a hard time understanding what i want. So here's pretty pictures that explains it in excruciating detail.
首先将交易加入奇怪:
到目前为止的结果
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 联接剩余的行:
Now attempt to join remaining rows with 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 :
Finally, join any leftover rows to 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
以及如何查看剩下的行:
And how look at the rows we have left over:
给我我想要的结果集
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
我有一个主表:
i have a main table:
Transactions
+----------+
| |
| |
| |
| |
| |
| |
| |
+----------+
我希望该表中的每一行仅联接一个可能的匹配表:
i want each row in this table to join only one possible matching table:
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 || Down || Charmed的集合连接在一起:
Which normally i'd perform as a join of Transactions to the set of Strange || Down || Charmed:
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)
)
问题是我希望联接按照首选顺序进行:
The problem is that i want the join to happen in that preferred order:
-
Strange -
Down -
Charmed
StrangeDownCharmed
单个事务完全有可能在多个表中具有匹配的条目.但是对于Transactions的每个其他表可能的 JOIN ，我希望SQL Server 首选 Strange表.如果没有匹配项，请转到Down表.如果没有匹配项，请转到Charmed表.
It's entirely possible that a single Transaction can have matching entries in multiple tables. But for each possible JOIN of Transactions to the other tables, i want SQL Server to prefer the Strange table. If there's no match then go to the Down table. If there's no match to go the Charmed table.
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)子句:
i've thought about using an OPTION(FORCE ORDER) clause:
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加入
But i don't want to force SQL Server to join
-
Transactions==>NationalSecurityLetters，这可能会更有利于加入 -
NationalSecurityLetters==>Transactions
Transactions==>NationalSecurityLetters, when it may be more advantageous to joinNationalSecurityLetters==>Transactions
推荐答案
也许此解决方案将为您提供帮助:
Maybe this solution will help you:
SET ANSI_WARNINGS ON;
GO
BEGIN TRAN;
CREATE TABLE dbo.TableA (
TableAID INT PRIMARY KEY,
DescriptionA VARCHAR(50) NOT NULL
);
INSERT dbo.TableA
VALUES (1,'A-1'), (2,'A-2');
CREATE TABLE dbo.TableB (
TableBID INT PRIMARY KEY,
DescriptionB VARCHAR(50) NOT NULL
);
INSERT dbo.TableB
VALUES (1,'B-1'), (2,'B-2'), (4,'B-4');
CREATE TABLE dbo.TableC (
TableCID INT PRIMARY KEY,
DescriptionC VARCHAR(50) NOT NULL
);
INSERT dbo.TableC
VALUES (1,'C-1'),(3,'C-3'), (4,'C-4');
GO
CREATE TABLE dbo.[Transaction] (
TransactionID INT IDENTITY PRIMARY KEY,
TranDate DATE NOT NULL,
Col1 INT NULL
);
INSERT dbo.[Transaction]
VALUES ('20120101', 1), ('20120202',2), ('20120303',3), ('20120404',4), ('20120505',5);
GO
SELECT *
FROM dbo.[Transaction] t
OUTER APPLY (
SELECT * FROM TableA a WHERE t.Col1=a.TableAID
) j1 --first join
OUTER APPLY (
SELECT * FROM TableB b WHERE j1.TableAID IS NULL AND t.Col1=b.TableBID --First condition will force the join order (dbo.TableA.TableAID should be NOT NULL)
) j2 --second join
OUTER APPLY (
SELECT * FROM TableC c WHERE j1.TableAID IS NULL AND j2.TableBID IS NULL AND t.Col1=c.TableCID ---First two conditions will force the join order (dbo.TableA.TableAID & dbo.TableB.TableBID should be NOT NULL)
) j3 --third join
WHERE j1.TableAID IS NOT NULL
OR j2.TableBID IS NOT NULL
OR j3.TableCID IS NOT NULL
ROLLBACK;
在这种情况下，加入顺序为:
1)t.Col1 = a.TableAID
1) t.Col1=a.TableAID
2)如果不是1)，则t.Col1 = b.TableBID
2) if not 1) then t.Col1=b.TableBID
3)如果不是1)和2)，则t.Col1 = c.TableCID
3) if not 1) and 2) then t.Col1=c.TableCID
结果:
TransactionID TranDate Col1 TableAID DescriptionA TableBID DescriptionB TableCID DescriptionC
------------- ---------- ---- -------- ------------ -------- ------------ -------- ------------
1 2012-01-01 1 1 A-1 NULL NULL NULL NULL
2 2012-02-02 2 2 A-2 NULL NULL NULL NULL
3 2012-03-03 3 NULL NULL NULL NULL 3 C-3
4 2012-04-04 4 NULL NULL 4 B-4 NULL NULL
这篇关于在SQL Server中强制执行部分连接顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
