连接两个表。选择一张表中的所有行，只选择另一张表中的匹配值？

debugcn 发表于 Dev

自行车男

我有以下表格：

Locations
+-------------+----------------+
| Location_ID | Location_Name  |
+-------------+----------------+
|           1 | Administration |
|           2 | Parking        |
|           3 | Warehouse      |
|           4 | Shipping       |
|           5 | Factory        |
|           6 | Office         |
|           7 | Processing     |
+-------------+----------------+

Item_Quantity
+---------+-------------+-------------------+
| Item_ID | Location_ID | Location_Quantity |
+---------+-------------+-------------------+
|       1 |           3 |                10 |
|       1 |           5 |                50 |
|       2 |           3 |                 7 |
+---------+-------------+-------------------+

我正在尝试获取具有指定 Item_ID 的 Location_Quantity 的所有 Location_ID 和 Location_Names 的列表。

Item_ID = 1 的预期结果是：

+-------------+----------------+-------------------+
| Location_ID | Location_Name  | Location_Quantity |
+-------------+----------------+-------------------+
|           1 | Administration |                 0 |
|           2 | Parking        |                 0 |
|           3 | Warehouse      |                10 |
|           4 | Shipping       |                 0 |
|           5 | Factory        |                50 |
|           6 | Office         |                 0 |
|           7 | Processing     |                 0 |
+-------------+----------------+-------------------+

Item_ID = 2 的预期结果是：

+-------------+----------------+-------------------+
| Location_ID | Location_Name  | Location_Quantity |
+-------------+----------------+-------------------+
|           1 | Administration |                 0 |
|           2 | Parking        |                 0 |
|           3 | Warehouse      |                 7 |
|           4 | Shipping       |                 0 |
|           5 | Factory        |                 0 |
|           6 | Office         |                 0 |
|           7 | Processing     |                 0 |
+-------------+----------------+-------------------+

我尝试了以下查询：

SELECT l.Location_ID, l.Location_Name, iq.Location_Quantity
FROM Locations l
LEFT JOIN Item_Quantity iq ON l.Location_ID = iq.Location_ID
WHERE iq.Item_ID = @Item_ID

SELECT l.Location_ID, l.Location_Name, iq.Location_Quantity
FROM Item_Quantity iq
LEFT JOIN Locations l ON l.Location_ID = iq.Location_ID
WHERE iq.Item_ID = @Item_ID

SELECT l.Location_ID, l.Location_Name, Location_Quantity = iif(iq.Location_Quantity IS NOT NULL, iq.Location_Quantity, 0)
FROM Locations l
LEFT JOIN Item_Quantity iq ON l.Location_ID = iq.Location_ID
WHERE iq.Item_ID = @Item_ID

所有查询仅返回条目在 Item_Quantity 中的行。

对于上述任何查询，这就是我为 Item_ID = 1 得到的结果：

+-------------+----------------+-------------------+
| Location_ID | Location_Name  | Location_Quantity |
+-------------+----------------+-------------------+
|           3 | Warehouse      |                10 |
|           5 | Factory        |                50 |
+-------------+----------------+-------------------+

我原以为 Locations 表上的 Left Join 会给我指定列中的所有行，但我一定是理解错误？

谁能看到我在这里做错了什么？

戈登·利诺夫

条件需要放在ON子句中。否则，该WHERE子句会将外连接转换为内连接。

您还想将 the 转换NULL为 a 0，因此请使用COALESCE()：

SELECT l.Location_ID, l.Location_Name, COALESCE(iq.Location_Quantity, 0) as Location_Quantity
FROM Locations l LEFT JOIN
     Item_Quantity iq
     ON l.Location_ID = iq.Location_ID AND iq.Item_ID = @Item_ID;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。