使用 tsql 中的案例语句连接

Question

我有表：

CREATE TABLE MyTable (
    RootId int,
    Direction bit,
   ....
);

现在，我必须编写从此表中选择并将一些表连接到其中。连接的表取决于方向参数。如何加入 MyTable3，如下所示：

 select 
  Root,
  Direction,
  Type
 from MyTable
 join MyTable1 on 
   MyTable1.Id = RootId
 join MyTable2 on 
   MyTable2.Id = RootId
 join MyTable3 on 
   ...
   case select when Direction = 1
     MyTable3.TypeId = MyTable1.TypeId
   else
     MyTable3.TypeId = MyTable2.TypeId

Answer 1

CASE表达式的谓词(即CASE表达式生成的谓词(不能是相等条件，而必须是值。 您可以按如下方式编写最终连接条件：

INNER JOIN MyTable3 t3
    ON (Direction = 1 AND t3.TypeId = t1.TypeId) OR
       (Direction <> 1 AND t3.TypeId = t2.TypeId)

以下是完整查询：

SELECT 
    Root,
    Direction,
    Type
FROM MyTable t
INNER JOIN MyTable1 t1
    ON t1.Id = t.RootId
INNER JOIN MyTable2 t2
    ON t2.Id = t.RootId
INNER JOIN MyTable3 t3
    ON (Direction = 1 AND t3.TypeId = t1.TypeId) OR
       (Direction <> 1 AND t3.TypeId = t2.TypeId);

Answer 2

出于性能原因，您可能希望使用两个left join，如下所示：

select Root,Direction,
      coalesce(m3_1.Type, m3_2.Type) as type
from MyTable join
     MyTable1 
     on MyTable1.Id = MyTable.RootId join
     MyTable2
     on MyTable2.Id = MyTable.RootId left join
     MyTable3 m3_1
     on m3_1.Direction = 1 and
        m3_1.TypeId = MyTable1.TypeId left join
     MyTable3 m3_0
     on m3_2.Direction = 1 and
        me_2.TypeId = MyTable2.TypeId;

使用 or 或 case(或实际上在on子句中使用and以外的任何内容(会对性能产生重大影响。