如果行中的初始值在 SQL 中为 < 或 > 0，如何返回单独的列总和

传奇xv2

我正在尝试编写一个 SQL 查询，该查询将根据值是负数还是正数返回每个客户的发货零件号总和的唯一行。例如：

如果我们两次向客户 A 运送了 20 件 Z 零件，他们将 6 件退回给我们，它会显示：

+-----------------+-------------+---------+
| Customer_Number | Part_Number | Shipped |
+-----------------+-------------+---------+
| A               | Z           |      20 |
| A               | Z           |      20 |
| A               | Z           |      -6 |
+-----------------+-------------+---------+

运行查询后，预期结果：

+-----------------+-------------+---------+
| Customer_Number | Part_Number | Shipped |
+-----------------+-------------+---------+
| A               | Z           |      40 |
| A               | Z           |      -6 |
+-----------------+-------------+---------+

我提出的查询是：

SELECT 
    Customer_Number,
    Part_Number,
    CASE
        WHEN Ship_Quantity > 0 THEN SUM(Ship_Quantity)
        WHEN Ship_Quantity < 0 THEN SUM(Ship_Quantity)
    END
FROM Sales_Line
GROUP BY
    Customer_Number,
    Part_Number

但我得到错误：

列无效。必须是按列分组：SELECT LIST 中的 Ship_Quantity。

当我将“Ship_Quantity”添加到我的 GROUP BY 时，它没有给出准确的结果：

原始输入：

+-----------------+---------------+---------+
| Customer_Number |  Part_Number  | Shipped |
+-----------------+---------------+---------+
| A080            | C76           | -11.0   |
| A080            | C76           | -1.0    |
| A080            | C76           | -2.0    |
| A080            | C76           | -1.0    |
| A080            | C76           | -1.0    |
| A080            | C76           | 21.0    |
| A080            | C76           | 79.0    |
| A080            | C76           | 1.0     |
| A080            | C76           | 11.0    |
| A080            | C76           | 99.0    |
| A045            | X150          | -6.0    |
| A045            | X150          | -1.0    |
| A045            | X150          | -11.0   |
| A045            | X150          | -2.0    |
| A045            | X150          | -1.0    |
| A045            | X150          | -1.0    |
| A045            | X150          | -1.0    |
| A045            | X150          | 373.0   |
| A045            | X150          | 12.0    |
| A045            | X150          | 1.0     |
| A045            | X150          | 300.0   |
| A045            | X150          | 146.0   |
| A045            | X150          | 150.0   |
| A045            | X150          | 150.0   |
| A045            | X150          | 200.0   |
| A045            | X150          | 150.0   |
| A045            | X150          | 150.0   |
+-----------------+---------------+---------+

查询后：

+-----------------+---------------+---------+
| Customer_Number |  Part_Number  | Shipped |
+-----------------+---------------+---------+
| A045            | X150          | 300.0   |
| A045            | X150          | 373.0   |
| A080            | C76           | -11.0   |
| A080            | C76           | -2.0    |
| A080            | C76           | -3.0    |
+-----------------+---------------+---------+

我该怎么做呢？

福帕斯

SIGN()在GROUP BY子句中使用函数：

SELECT Customer_Number, Part_Number,
       SUM(Shipped) Shipped
FROM Sales_Line
GROUP BY Customer_Number, Part_Number, SIGN(Shipped)

如果您的数据库不支持该函数，请SIGN()使用CASE表达式：

SELECT Customer_Number, Part_Number,
       SUM(Shipped) Shipped
FROM Sales_Line
GROUP BY Customer_Number, Part_Number, 
         CASE WHEN Shipped < 0 THEN -1 ELSE 1 END

请参阅演示。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。