时间戳

首页 > 解决方案 > AND/OR 运算符未按需要过滤

sql - AND/OR 运算符未按需要过滤

问题描述

我正在尝试提取标记为“库存”（状态=“I”）的卡车列表，这些卡车没有“P”、“B”或“F”的“FiWipStatusCode”。

换句话说，我想提取一个标记为库存的卡车列表，这些卡车的“fiwipstatuscode”为“null”或“C”。

下面的代码没有按照我想要的方式过滤。谢谢！

SELECT     InventoryVehicle.StockNo, InventoryVehicle.Status, VehicleSales.FiWipStatusCode
FROM       InventoryVehicle
INNER JOIN VehicleSales ON InventoryVehicle.StockNo = VehicleSales.StockNo
WHERE      (InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode <> 'F') AND (InventoryVehicle.Balance > '15000') OR
           (InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode <> 'P') AND (InventoryVehicle.Balance > 15000) OR
           (InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode <> 'B') AND (InventoryVehicle.Balance > 15000) OR
           (InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode IS NULL) AND (InventoryVehicle.Balance > 15000) OR
           (InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode = 'C') AND (InventoryVehicle.Balance > 15000)

标签： sqlsql-serveroperators

解决方案

您混淆了 AND 和 OR 的先例。我想你可能想要这个：

SELECT InventoryVehicle.StockNo, InventoryVehicle.Status, VehicleSales.FiWipStatusCode
FROM InventoryVehicle INNER JOIN VehicleSales ON InventoryVehicle.StockNo = VehicleSales.StockNo
WHERE ((InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode <> 'F') AND (InventoryVehicle.Balance > '15000')) OR
                  ((InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode <> 'P') AND (InventoryVehicle.Balance > 15000)) OR
                  ((InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode <> 'B') AND (InventoryVehicle.Balance > 15000)) OR
                  ((InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode is NULL) AND (InventoryVehicle.Balance > 15000)) OR
                  ((InventoryVehicle.Status = 'I') AND (VehicleSales.FiWipStatusCode = 'C') AND (InventoryVehicle.Balance > 15000));

注意 () 封装了每组 AND 检查。

并且由于现在封装了每组 AND，因此您可以使其变得更加容易：

SELECT InventoryVehicle.StockNo, InventoryVehicle.Status, VehicleSales.FiWipStatusCode
FROM InventoryVehicle INNER JOIN VehicleSales ON InventoryVehicle.StockNo = VehicleSales.StockNo
WHERE (InventoryVehicle.Status = 'I' AND VehicleSales.FiWipStatusCode <> 'F' AND InventoryVehicle.Balance > '15000') OR
      (InventoryVehicle.Status = 'I' AND VehicleSales.FiWipStatusCode <> 'P' AND InventoryVehicle.Balance > 15000) OR
      (InventoryVehicle.Status = 'I' AND VehicleSales.FiWipStatusCode <> 'B' AND InventoryVehicle.Balance > 15000) OR
      (InventoryVehicle.Status = 'I' AND VehicleSales.FiWipStatusCode is NULL AND InventoryVehicle.Balance > 15000) OR
      (InventoryVehicle.Status = 'I' AND VehicleSales.FiWipStatusCode = 'C' AND InventoryVehicle.Balance > 15000);

而且，由于两种过滤器参数在每种情况下都相同，因此您可以进一步简化它：

SELECT InventoryVehicle.StockNo, InventoryVehicle.Status, VehicleSales.FiWipStatusCode
FROM InventoryVehicle INNER JOIN VehicleSales ON InventoryVehicle.StockNo = VehicleSales.StockNo
WHERE InventoryVehicle.Status = 'I'
AND InventoryVehicle.Balance > '15000'
AND (
    VehicleSales.FiWipStatusCode <> 'F' OR
    VehicleSales.FiWipStatusCode <> 'P' OR
    VehicleSales.FiWipStatusCode <> 'B' OR
    VehicleSales.FiWipStatusCode is NULL OR
    VehicleSales.FiWipStatusCode = 'C'
);

推荐阅读