sql-server - 如何选择具有不同条件的同一列？

Question

我正在尝试从两个表中选择数据。
-warehouse 有两列:warehouseId、warehouseName
-transportation 有三列:transporter、warehouseId1、warehouseId2
我想从两个表中选择并从表 A 中获取warehouseId1 和warehouseId2 的warehouseName这是我的代码，但它不起作用。

select a.transporter, b.warehouseName as warehouse1, b.warehouseName as warehouse2
from transportation a, warehouse b
where a.warehouseId1 = b.warehouseId and a.warehouseId2 = b.warehouseId

Answer 1

您必须将 warehouse 两次添加到 FROM 中(只需记住为它们使用两个不同的别名):

SELECT
    a.transporter,
    b1.warehouseName as warehouse1,
    b2.warehouseName as warehouse2
FROM
    transportation a,
    warehouse b1,
    warehouse b2
WHERE
        a.warehouseId1 = b1.warehouseId
    AND
        a.warehouseId2 = b2.warehouseId

或使用JOIN语法:

SELECT
    a.transporter,
    b1.warehouseName AS warehouse1,
    b2.warehouseName AS warehouse2
FROM
    transportation a
JOIN
    warehouse b1 ON a.warehouseId1 = b1.warehouseId
JOIN
    warehouse b2 ON a.warehouseId2 = b2.warehouseId