SQL中NULL的妙用

商品表Products

库房表WarehouseDistrict

库存表WarehouseStock

 

一般写法

DECLARE @districtId INT = 2454

;WITH stock
AS
(
    SELECT DistrictId, ProductId
        , ws.Inventory
    FROM dbo.WarehouseDistrict wd
    INNER JOIN dbo.WarehouseStock ws 
        ON ws.IsOffMarket = 0

        AND wd.WarehouseId = ws.WarehouseId

    WHERE wd.IsDelete = 0
        AND (0 = ISNULL(@districtId, 0) OR wd.DistrictId = @districtId)
    GROUP BY DistrictId, ProductId, ws.Inventory
)
SELECT a.DistrictId, a.ProductId, ISNULL(b.Inventory, 0) Inventory
FROM  stock a
LEFT JOIN (SELECT DistrictId, ProductId, MIN(Inventory) Inventory
    FROM stock
    WHERE Inventory > 0 /*小于0的，忽略*/
    GROUP BY DistrictId, ProductId) b
     ON a.DistrictId = b.DistrictId
     AND a.ProductId = b.ProductId

利用聚合函数忽略NULL

DECLARE @districtId INT = 2454

SELECT DistrictId, ProductId
    , ISNULL( MIN(CASE WHEN ws.Inventory <= 0 THEN NULL ELSE ws.Inventory END), 0) Inventory
FROM dbo.WarehouseDistrict wd
INNER JOIN dbo.WarehouseStock ws 
    ON ws.IsOffMarket = 0
    --AND ws.Inventory > 0 /*小于0的，忽略*/

    AND wd.WarehouseId = ws.WarehouseId

WHERE wd.IsDelete = 0
    AND (0 = ISNULL(@districtId, 0) OR wd.DistrictId = @districtId)
GROUP BY DistrictId, ProductId