SQL JOIN WITH OR条件 [英] SQL JOIN WITH OR Condition

问题描述

我有一个表，说案例，该表使用了来自工人的参考信息.此外，还有一个表工人所属的公司表.

I have a table say Cases, which is using reference from Workers for three columns. Also there is one table Company to which workers belongs.

以下是架构:

Cases [ CaseID, CaseNumber, Worker1, Worker2, Worker3 ] 
Workers [ WorkerID, ComapnyID]
Company [CompanyID, CompanyName]

现在我需要每个公司的案件数. 因此，是否可以与worker进行连接并映射所有 Worker1 ， Worker2 和 Worker3 列?有更好的选择和性能影响吗?

Now I need case count for each company. So is it possible to make one join with workers and map all Worker1, Worker2 and Worker3 columns? Is there any better option and performance impact?

注意:一家公司的两名工人可以处理单个案件，或者所有工人可以来自不同的公司.

Note: Two workers from one company can work on single case, or all the workers can be from different companies.

推荐答案

尽管联接条件通常是相等性检查，但是它们没有什么特别的-任何有效的SQL条件都可以用于执行联接.对于您而言，IN条件似乎合适:

Although join conditions are commonly equality checks, there's nothing special about them - any valid SQL condition could be used for performing a join. In you case, an IN condition seems appropriate:

SELECT   CompanyName, COUNT(DISTINCT CaseID)
FROM     Company co
JOIN     Workers w ON co.CompanyId = w.CompanyId
JOIN     Cases ca ON w.WorkerId IN (ca.Worker1, ca.Worker2, ca.Worker3)
GROUP BY CompanyName

这篇关于SQL JOIN WITH OR条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！