基于列值的 SQL Server 条件联接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15660765/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server conditional join based on column values
提问by ebooyens
I'm working on a fairly complicated aspect to an Equipment / Asset database in SQL Server and would like to ask for some help please. I'll try and focus on one issue here and then ask a separate question for another problem.
我正在处理 SQL Server 中设备/资产数据库的一个相当复杂的方面，并想寻求帮助。我将尝试在这里关注一个问题，然后针对另一个问题提出一个单独的问题。
I have a table for equipment called tblEquipmentand a table for required actions that need to be performed on collections of equipment (tblActionsRequired), for the sake of this question the relevant fields are:
我有一个称为设备tblEquipment的表和一个需要对设备集合执行的所需操作的表 ( tblActionsRequired)，为了这个问题，相关字段是：
tblEquipment:EquipmentID, BasedAtID, AreaIDtblRequiredActions:AllSites (bit), BasedAtID, AreaID
tblEquipment：EquipmentID, BasedAtID, AreaIDtblRequiredActions：AllSites (bit), BasedAtID, AreaID
So the idea with tblRequiredActionsis that you'd say all equipment at company Site A needs to be inspected every so often. Areas are specific rooms or offices etc. at a site. So if AllSites is true the action applies to all equipment company-wide, if it's false then BasedAtID (for the site) is required, AreaID is optional if you want to narrow it down even more.
所以这个想法tblRequiredActions是你会说公司站点 A 的所有设备都需要经常检查。区域是站点上的特定房间或办公室等。因此，如果 AllSites 为 true，则该操作适用于公司范围内的所有设备，如果为 false，则需要 BaseAtID（对于站点），如果您想进一步缩小范围，则 AreaID 是可选的。
So now the problem to extract which actions should be applied to which equipment based on these three fields.
所以现在的问题是根据这三个领域来提取哪些动作应该应用于哪些设备。
What I have now is what I think might work but I'm struggling to verify my results as there are other factors confusing me as well, so would really appreciate some confirmation or guidance if I'm completely on the wrong track! I don't want to go the route of stored procedures and if blocks or unions as there are multiple other dimensions that need to be covered with the similar principle and I'll end up writing a massively complicated procedure that will be a nightmare to maintain. Thanks!!
我现在所拥有的是我认为可能有效的方法，但我正在努力验证我的结果，因为还有其他因素让我感到困惑，所以如果我完全走错了路，我真的很感激一些确认或指导！我不想走存储过程和 if 块或联合的路线，因为还有多个其他维度需要用类似的原则覆盖，我最终会编写一个非常复杂的过程，这将是一场噩梦。 . 谢谢！！
SELECT
dbo.tblActionsRequired.ActionID, dbo.tblEquipment.EquipmentID
FROM
dbo.tblEquipment
INNER JOIN
dbo.tblActionsRequired ON dbo.tblActionsRequired.AllSites = 'True'
OR dbo.tblEquipment.AreaID IS NULL
AND dbo.tblEquipment.BasedAtID = dbo.tblActionsRequired.BasedAtID
OR dbo.tblEquipment.AreaID IS NOT NULL
AND dbo.tblEquipment.AreaID = dbo.tblActionsRequired.AreaID
采纳答案by ebooyens
The existing query looks pretty good to me, although it can be simplified slightly:
现有的查询对我来说看起来不错，尽管可以稍微简化一下：
SELECT dbo.tblActionsRequired.ActionID, dbo.tblEquipment.EquipmentID
FROM dbo.tblEquipment
JOIN dbo.tblActionsRequired
ON dbo.tblActionsRequired.AllSites = 'True' OR
(dbo.tblEquipment.AreaID IS NULL AND
dbo.tblEquipment.BasedAtID = dbo.tblActionsRequired.BasedAtID) OR
dbo.tblEquipment.AreaID = dbo.tblActionsRequired.AreaID
- since if both a.AreaID and e.AreaID are null, then the condition dbo.tblEquipment.AreaID = dbo.tblActionsRequired.AreaIDdoes not evaluate as true.
- 因为如果 a.AreaID 和 e.AreaID 都为空，则条件dbo.tblEquipment.AreaID = dbo.tblActionsRequired.AreaID不会评估为真。
(The parentheses aren't strictly required, but were included for clarity.)
（括号不是严格要求的，但为了清楚起见包括在内。）
回答by Sepster
Looks correct to me; Your logic and approach look sound.
对我来说看起来是正确的；你的逻辑和方法看起来很合理。
Although the combinations of AND/OR might be open to misinterpretation... Perhaps consider grouping your AND expressions in parenthesis (I know this is redundant, but doing so clarifies your intent). And as per @MarkBannister's recent answer, this might reveal an opportunity for Boolean algebra factorization :-)
虽然 AND/OR 的组合可能容易被误解......也许可以考虑将您的 AND 表达式分组在括号中（我知道这是多余的，但这样做可以澄清您的意图）。根据@MarkBannister 最近的回答，这可能揭示了布尔代数分解的机会:-)
I'd personally move any "filtering" out of the inner join's ON clause, into a WHERE clause, leaving just the expressions that relate to the join itself, in the case where there's only one join and those filters are isolated from the join logic itself. But this is not the case here.
我个人将任何“过滤”从内部连接的 ON 子句移到 WHERE 子句中，只留下与连接本身相关的表达式，在只有一个连接并且这些过滤器与连接逻辑隔离的情况下本身。但这里的情况并非如此。
Indexes will probably affect this one way or the other... Check your execution plan to see if you'd benefit from adding some on your FKs or the text field.
索引可能会以一种或另一种方式影响这种情况......检查您的执行计划，看看您是否会从在 FK 或文本字段上添加一些受益。
回答by paparazzo
I don't think you need the not null
If AreaID is null then dbo.tblEquipment.AreaID = dbo.tblActionsRequired.AreaID will evaluate to null
我认为你不需要 not null
如果 AreaID 为 null 那么 dbo.tblEquipment.AreaID = dbo.tblActionsRequired.AreaID 将评估为 null
SELECT
dbo.tblActionsRequired.ActionID, dbo.tblEquipment.EquipmentID
FROM
dbo.tblEquipment
INNER JOIN
dbo.tblActionsRequired
ON dbo.tblActionsRequired.AllSites = 'True'
OR dbo.tblEquipment.AreaID IS NULL
AND dbo.tblEquipment.BasedAtID = dbo.tblActionsRequired.BasedAtID
OR dbo.tblEquipment.AreaID = dbo.tblActionsRequired.AreaID
