MSSQL条件行选择求和：优先取LineStatus=80，无则取10

数据示例 #

需求说明 #

对相同SO和LineNumber的分组：

1. 优先选择LineStatus=80的行，计算SUM(OrderQty)
2. 若分组内无LineStatus=80的行，则选择LineStatus=10的行计算求和

解决方案 #

方法1：窗口函数（推荐，灵活适配复杂场景）

利用ROW_NUMBER()给行按优先级排序，只保留每个分组内优先级最高的行再求和：

WITH RankedLines AS (
    SELECT 
        SO,
        LineNumber,
        OrderQty,
        ROW_NUMBER() OVER (
            PARTITION BY SO, LineNumber 
            ORDER BY CASE LineStatus WHEN 80 THEN 1 WHEN 10 THEN 2 ELSE 3 END
        ) AS rn
    FROM YourTableName
    WHERE LineStatus IN (10, 80)
)
SELECT 
    SO,
    LineNumber,
    SUM(OrderQty) AS TotalOrderQty
FROM RankedLines
WHERE rn = 1
GROUP BY SO, LineNumber;

• 逻辑：PARTITION BY按SO+LineNumber分组，CASE给LineStatus=80分配最高优先级（排序值1），rn=1筛选出每个分组内的优先行，最后聚合求和。

方法2：EXISTS条件判断（适合简单场景）

通过EXISTS判断同一分组是否存在LineStatus=80的行，再筛选符合条件的行求和：

SELECT 
    SO,
    LineNumber,
    SUM(OrderQty) AS TotalOrderQty
FROM YourTableName t1
WHERE LineStatus = 80
OR (
    LineStatus = 10 
    AND NOT EXISTS (
        SELECT 1 
        FROM YourTableName t2
        WHERE t2.SO = t1.SO 
          AND t2.LineNumber = t1.LineNumber 
          AND t2.LineStatus = 80
    )
)
GROUP BY SO, LineNumber;

• 逻辑：先选中所有LineStatus=80的行；对LineStatus=10的行，仅保留那些同一分组下无80状态的行，最后聚合求和。

方法3：聚合函数+优先级判断

先确定每个分组的优先状态，再筛选对应状态的行求和：

SELECT 
    SO,
    LineNumber,
    SUM(OrderQty) AS TotalOrderQty
FROM (
    SELECT 
        SO,
        LineNumber,
        LineStatus,
        OrderQty,
        MAX(CASE LineStatus WHEN 80 THEN 80 ELSE 10 END) OVER (
            PARTITION BY SO, LineNumber
        ) AS PreferredStatus
    FROM YourTableName
    WHERE LineStatus IN (10, 80)
) t
WHERE LineStatus = PreferredStatus
GROUP BY SO, LineNumber;

• 逻辑：内层查询通过MAX()确定每个分组的优先状态（有80则取80，否则取10），外层筛选出匹配优先状态的行后求和。

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