如何根据另一列中的类型标识调用不同但在同一列中的信息

Question

我有如下数据：

​

​

它是Equipment_ID下的驾驶员ID和拖拉机(卡车) ID，其中Equipment_Type D=驾驶员和T=拖拉机都与移动ID相关联。

我想说if Equipment Type = 'D‘then Equipment_ID = kimjames和if Equipment_Type = 'T’then 49085，但我不确定如何在嵌套在select语句中或嵌套在另一个where语句中的Where语句中做到这一点。

这是在一个更大的查询中，表通过移动ID连接。表中还有更多的条目，这只是我正在处理的数据的一个示例。

下面是我正在使用的atm查询：

select distinct
    Movement.ID as 'Movement ID',
    Tractor.ID as 'Tractor',
    Driver.ID as 'Owner',
    Payee.Legal_Name as 'Name',
    Driver.Payee_ID as 'Team Driver',
    sum(Movement.Move_Distance) as 'Total',
    Driver.Type_of as 'Driver Type'

from
    Driver
full outer join
    Continuity on Continuity.Equipment_ID = Driver.ID
full outer join
    Movement on Movement.ID = Continuity.Movement_ID
full outer join
    Tractor on Tractor.ID = Continuity.Equipment_ID
inner join
    Payee on Payee.ID = Driver.ID

where 
    Driver.Company_ID = 'TMS'
    and Driver.Is_Active ='Y'
    and Driver.Type_of ='C'
    and Movement.Company_ID = 'TMS'
    and Movement.ID = Continuity.Movement_ID
    and Continuity.Equipment_Type_ID = 'D'
    and Driver.ID = Continuity.Equipment_ID
    and Continuity.Company_ID = 'TMS'
    and Xfer2settle_date between '03/18/2016' and '03/30/2016' 

group by
    Movement.ID,
    Tractor.ID,
    Driver.ID,
    Payee.Legal_Name,
    Driver.Payee_ID,
    Movement.Move_Distance,
    Driver.Type_of

order by Driver.ID

当前，当我运行此命令时，我得不到拖拉机信息，因为如果我尝试添加：

and Continuity.Equipment_Type_ID = 'T'
and Tractor.ID = Continuity.Equipment_ID

到Where条件，则它与以下内容冲突：

and Continuity.Equipment_Type_ID = 'D'
and Driver.ID = Continuity.Equipment_ID

而且我在连接中已经有了Tractor.ID = Continuity.Equipment_ID。

不确定如何对其进行编码，以便从样本所在的连续表中提取拖拉机ID。

从连续性表中提取的唯一列是第2列和第3列，但单行的结果如下所示：

​

​

Answer 1

这是你想要的吗？

where ( (Equipment_Type = 'D' and Equipment_ID = 'KIMJAMES') or
        (Equipment_Type = 'T' and Equipment_ID = '49085')
      )