SQL JOIN WITH OR 条件
我有一个表格，上面写着 Cases，它使用了来自 Workers 的三列引用.还有一张表公司是工人所属的.
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]
现在我需要每个公司的案例计数.那么是否可以与工人建立一个连接并映射所有 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
相关文章