如何在SQL中过滤销售额为0的订单并计算聚合指标？

对应你的DAX逻辑：过滤单个销售额为0的明细行 #

你的DAX是筛选出Net Sales不为0的订单明细后再聚合指标，对应到SQL里，只需要在WHERE子句里加上对明细行的过滤，但要注意处理NULL值和浮点精度问题：

SELECT
    [Item Number],
    SUM(od.[Net Sales]) AS "Net Sales",
    SUM(od.[Quantity Shipped]) AS "Quantity",
    SUM(od.[Commission Amount]) AS "Commission",
    SUM(op.[GP]) AS "GP",
    SUM(op.[GPAC]) AS "GPAC"
FROM [ORDERS] AS "o"
INNER JOIN [ORDER DETAIL] AS "od" ON o.[Order ID] = od.[Order Id]
INNER JOIN [Order Detail Profit] AS "op" ON od.[Order Detail ID] = op.[Order Detail ID]
INNER JOIN [CUSTOMER] AS "c" ON c.[Customer ID] = o.[Customer ID]
WHERE 
    DATEDIFF(MM, [INVOICE DATE], GETDATE()) <= 12
    AND [Account Type] = 'REGULAR'
    -- 过滤NULL和0的明细行，浮点型用范围判断避免精度误差
    AND od.[Net Sales] IS NOT NULL 
    AND ABS(od.[Net Sales]) > 0.0001
GROUP BY [Item Number]
ORDER BY [Item Number]

如果Net Sales是整数类型，直接用od.[Net Sales] <> 0即可，无需ABS判断。

满足核心需求：排除整个销售额为0的订单 #

如果你的目标是只要订单总销售额为0（比如全是免费赠送的商品），就排除该订单的所有数据，那需要先筛选出有效订单（总销售额不为0），再关联查询：

用CTE写法（可读性更高） #

WITH valid_orders AS (
    -- 先找出总销售额不为0的订单ID
    SELECT [Order ID]
    FROM [ORDER DETAIL]
    GROUP BY [Order ID]
    HAVING SUM([Net Sales]) IS NOT NULL AND ABS(SUM([Net Sales])) > 0.0001
)
SELECT
    [Item Number],
    SUM(od.[Net Sales]) AS "Net Sales",
    SUM(od.[Quantity Shipped]) AS "Quantity",
    SUM(od.[Commission Amount]) AS "Commission",
    SUM(op.[GP]) AS "GP",
    SUM(op.[GPAC]) AS "GPAC"
FROM valid_orders
INNER JOIN [ORDERS] AS "o" ON valid_orders.[Order ID] = o.[Order ID]
INNER JOIN [ORDER DETAIL] AS "od" ON o.[Order ID] = od.[Order Id]
INNER JOIN [Order Detail Profit] AS "op" ON od.[Order Detail ID] = op.[Order Detail ID]
INNER JOIN [CUSTOMER] AS "c" ON c.[Customer ID] = o.[Customer ID]
WHERE 
    DATEDIFF(MM, [INVOICE DATE], GETDATE()) <= 12
    AND [Account Type] = 'REGULAR'
GROUP BY [Item Number]
ORDER BY [Item Number]

用子查询写法 #

SELECT
    [Item Number],
    SUM(od.[Net Sales]) AS "Net Sales",
    SUM(od.[Quantity Shipped]) AS "Quantity",
    SUM(od.[Commission Amount]) AS "Commission",
    SUM(op.[GP]) AS "GP",
    SUM(op.[GPAC]) AS "GPAC"
FROM 
    (SELECT [Order ID]
     FROM [ORDER DETAIL]
     GROUP BY [Order ID]
     HAVING SUM([Net Sales]) IS NOT NULL AND ABS(SUM([Net Sales])) > 0.0001) AS valid_orders
INNER JOIN [ORDERS] AS "o" ON valid_orders.[Order ID] = o.[Order ID]
INNER JOIN [ORDER DETAIL] AS "od" ON o.[Order ID] = od.[Order Id]
INNER JOIN [Order Detail Profit] AS "op" ON od.[Order Detail ID] = op.[Order Detail ID]
INNER JOIN [CUSTOMER] AS "c" ON c.[Customer ID] = o.[Customer ID]
WHERE 
    DATEDIFF(MM, [INVOICE DATE], GETDATE()) <= 12
    AND [Account Type] = 'REGULAR'
GROUP BY [Item Number]
ORDER BY [Item Number]

你之前的尝试未生效的可能原因 #

1. NULL值未处理：如果od.[Net Sales]存在NULL，NULL <> 0的结果是UNKNOWN，不会被过滤，必须加上IS NOT NULL
2. 需求理解偏差：你要排除的是整个订单，而不是单个明细行，单纯过滤明细行无法移除订单中其他非0的明细数据
3. 浮点精度问题：如果Net Sales是浮点型，0可能被存储为极小的数值（比如0.0000001），直接用<>0无法匹配，需要用范围判断

内容的提问来源于stack exchange，提问作者Steve Buscemi