sql - 根据另一个表中的列值选择一个表中的行？

Question

我需要为另一个表中的每个匹配行选择一列。听起来很简单，但有一个转折点。

我有一张包含公司 ID 并定义公司的表。

我有另一个表定义了这些公司的部门。

我有一个包含各种部门状态代码的第三个表。

我下面的代码工作得很好，正是我想要它做的。但是我必须将要查找的部门硬编码到查询中。我希望它根据它在 Departments 表中找到的部门代码进行子选择，而不需要我对该公司可能存在的每个可能的部门进行硬编码。

我需要根据 DepartmentsStatus 表中定义的公司存在哪些部门，从 DepartmentStatus 中选择匹配的部门状态。

我怀疑这是一个数据透视表，但它们有点高于我的水平。

(公司表)

Company_ID  Company_Name
-------------------------
1       Home Office
2       Stanton Office
3       NYC Office

(部门表)

CompanyID   Department_Code
----------------------------
1           Sales
1           Inventory
1           Retail
1           Maint

2           OtherDept
2           ThatDept
2           BobsDept

(部门状态表)

Company_ID  Department  StatusCode
-----------------------------------------
1           Sales       InReview
1           Inventory   InReview
1           Retail      Ready
1           Maint       Done
2           OtherDept   InReview
2           ThatDept    Research
2           BobsDept    InReview

注意:我使用“TOP 1”，即使 Company_ID+Department 上有唯一索引，因此匹配行永远不会超过一个。

因此，对于 Company_ID=1:

select  Company_ID,  
        (select top 1 StatusCode from DepartmentStatus ds where ds.Company_ID = cm.Company_ID and ds.Department='Sales') as SalesStatus
        (select top 1 StatusCode from DepartmentStatus ds where ds.Company_ID = cm.Company_ID and ds.Department='Inventory') as InvStatus
        (select top 1 StatusCode from DepartmentStatus ds where ds.Company_ID = cm.Company_ID and ds.Department='Retail') as RetailStatus
        (select top 1 StatusCode from DepartmentStatus ds where ds.Company_ID = cm.Company_ID and ds.Department='Main') as MaintStatus
from    Company cm
Where   cm.CompanyID=1

结果:

Company_ID      SalesStatus     InvStatus   RetailStatus    MaintStatus
--------------- --------------- ----------  -------------   ------------
1               InReview        InReview    Ready           Done

或者，对于 CompanyID=2:

select  Company_ID,  
        (select top 1 StatusCode from DepartmentStatus ds where ds.CompanyID = cm.Company_ID and ds.Department='OtherDept') as OtherDeptStatus
        (select top 1 StatusCode from DepartmentStatus ds where ds.CompanyID = cm.Company_ID and ds.Department='ThatDept') as ThatDeptStatus
        (select top 1 StatusCode from DepartmentStatus ds where ds.CompanyID = cm.Company_ID and ds.Department='BobsDept') as BobsDeptStatus
from    Company cm
Where   cm.CompanyID=2

结果:

Company_ID  OtherDeptStatus     ThatDeptStatus      BobsDeptStatus
----------  ----------------    --------------      --------------
2           InReview            Research            InReview

因此，对于公司 1，我需要获取部门销售、库存、零售和维护的状态。
但是对于公司 2，我需要获取 Departments OtherDept、ThatDept 和 BobsDept 的状态。

我认为描述我正在尝试做的步骤是:

从部门表中获取公司 1 的部门列表。

对于步骤 1 中的每个部门，查询 DepartmentStatus 表，以获取部门是否等于该部门。
对于公司 1，查询 get StatusCode for Departments "Sales, Inventory, Retail, and Maint"
对于公司 2，查询 get StatusCode for Departments“OtherDept、thatDept 和 BobsDept”。
等等等等

问题是，我不知道提前(在查询时)每个公司存在哪些部门，所以我需要根据每个公司实际存在的部门进行子选择。

还有一些其他的 S.O.已经接近我所要求的答案，建议使用 JOIN 来完成此操作，但是，它们都假设您在编写 join 语句时提前知道要查询的值。

我正在寻找解决此问题的方法，而不仅仅是对我当前尝试的更正。如果您对如何实现这一点有更好的想法，我很乐意看到它。

Answer 1

您似乎正在寻找条件聚合。使用此技术，您的查询可以简化如下，以避免需要多个内联子查询:

select  Company_ID,  
        max(case when ds.Department='Sales' then ds.StatusCode end) as SalesStatus,
        max(case when ds.Department='Inventory' then ds.StatusCode end) as InvStatus,
        max(case when ds.Department='Retail' then ds.StatusCode end) as RetailStatus,
        max(case when ds.Department='Main' then ds.StatusCode end) as MaintStatus
from    
    Company cm
    inner join DepartmentStatus ds on ds.Company_ID = cm.Company_ID 
Where   cm.CompanyID=1
group by cm.CompanyID