如何基于计算字段构建WHERE条件？库存仓SQL问题咨询

现有数据 #

Bin Code     Max Pallets     Total Pallets    Available
   000-AA-001   34              30               4
   000-BB-001   24              18               6
   000-CC-001   12              14               -2

原查询问题修正 #

原需求是筛选出Available>0的仓位编码，但原查询存在语法错误：WHERE子句不能直接引用SELECT中的计算列，必须用HAVING过滤分组后的结果。

修正后的原查询语句 #

SELECT 
    T0."BinCode", 
    T0."U_TRX_MaxPallets" AS "Max Pallets",
    SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) AS "Total Pallets", 
    T0."U_TRX_MaxPallets" - SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) AS "Available"
FROM OBIN T0  
INNER JOIN OIBQ T1 ON T0."AbsEntry" = T1."BinAbs"  
INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
WHERE 
    T1."OnHandQty" <> 0 
    AND T1."WhsCode" = '000' 
    AND SUBSTRING(T0."BinCode",5,1) <> 'R' 
    AND SUBSTRING(T0."BinCode",5,1) <> 'P' 
    AND SUBSTRING(T0."BinCode",1,11) <> '000-OFFICE' 
    AND SUBSTRING(T0."BinCode",1,8) <> '000-OTBD'
GROUP BY T0."BinCode", T0."U_TRX_MaxPallets"
HAVING T0."U_TRX_MaxPallets" - SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) > 0
ORDER BY T0."BinCode"

修改后汇总查询的报错解决 #

报错原因 #

当你从GROUP BY中移除T0."U_TRX_MaxPallets"时，SELECT中直接引用了这个非聚合列，违反SQL分组规则：SELECT中的列要么是GROUP BY成员，要么被聚合函数包裹。数据库无法确定返回哪个仓位的U_TRX_MaxPallets值，因此报错。

正确的Total汇总查询 #

如果需要生成所有符合条件仓位的汇总行，需对U_TRX_MaxPallets使用聚合函数（如SUM，根据业务需求选择）：

SELECT 
    'Total' AS "Bin Code", 
    SUM(T0."U_TRX_MaxPallets") AS "Max Pallets",
    SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) AS "Total Pallets", 
    SUM(T0."U_TRX_MaxPallets") - SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) AS "Available"
FROM OBIN T0  
INNER JOIN OIBQ T1 ON T0."AbsEntry" = T1."BinAbs"  
INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
WHERE 
    T1."OnHandQty" <> 0 
    AND T1."WhsCode" = '000' 
    AND SUBSTRING(T0."BinCode",5,1) <> 'R' 
    AND SUBSTRING(T0."BinCode",5,1) <> 'P' 
    AND SUBSTRING(T0."BinCode",1,11) <> '000-OFFICE' 
    AND SUBSTRING(T0."BinCode",1,8) <> '000-OTBD'
GROUP BY 'Total'

明细+汇总合并查询（可选） #

如果需要同时展示符合条件的仓位明细和汇总行，用UNION ALL合并两个查询：

-- 符合条件的仓位明细
SELECT 
    T0."BinCode", 
    T0."U_TRX_MaxPallets" AS "Max Pallets",
    SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) AS "Total Pallets", 
    T0."U_TRX_MaxPallets" - SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) AS "Available"
FROM OBIN T0  
INNER JOIN OIBQ T1 ON T0."AbsEntry" = T1."BinAbs"  
INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
WHERE 
    T1."OnHandQty" <> 0 
    AND T1."WhsCode" = '000' 
    AND SUBSTRING(T0."BinCode",5,1) <> 'R' 
    AND SUBSTRING(T0."BinCode",5,1) <> 'P' 
    AND SUBSTRING(T0."BinCode",1,11) <> '000-OFFICE' 
    AND SUBSTRING(T0."BinCode",1,8) <> '000-OTBD'
GROUP BY T0."BinCode", T0."U_TRX_MaxPallets"
HAVING T0."U_TRX_MaxPallets" - SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) > 0

UNION ALL

-- 汇总行
SELECT 
    'Total' AS "Bin Code", 
    SUM(T0."U_TRX_MaxPallets") AS "Max Pallets",
    SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) AS "Total Pallets", 
    SUM(T0."U_TRX_MaxPallets") - SUM(NDIV0(T1."OnHandQty", (T2."U_TRX_Ti" * T2."U_TRX_Hi"))) AS "Available"
FROM OBIN T0  
INNER JOIN OIBQ T1 ON T0."AbsEntry" = T1."BinAbs"  
INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
WHERE 
    T1."OnHandQty" <> 0 
    AND T1."WhsCode" = '000' 
    AND SUBSTRING(T0."BinCode",5,1) <> 'R' 
    AND SUBSTRING(T0."BinCode",5,1) <> 'P' 
    AND SUBSTRING(T0."BinCode",1,11) <> '000-OFFICE' 
    AND SUBSTRING(T0."BinCode",1,8) <> '000-OTBD'
GROUP BY 'Total'
ORDER BY 
    CASE WHEN "Bin Code" = 'Total' THEN 1 ELSE 0 END, 
    "Bin Code"

内容的提问来源于stack exchange，提问作者Brian Branco