时间戳

首页 > 解决方案 > 根据类型对具有相同 Id 的行求和，并排除 SUM = 0 的行

sql - 根据类型对具有相同 Id 的行求和，并排除 SUM = 0 的行

问题描述

我有这张桌子MOVEMENTS：

Id  |  FatherId  |  MovementType |   Quantity   |
=================================================
1   | A         |   IN          |   10          |
2   | A         |   IN          |   5           |
3   | A         |   OUT         |   5           |
4   | B         |   IN          |   10          |
5   | B         |   OUT         |   10          |
6   | C         |   IN          |   5           |

我正在尝试使用 IN - OUT Movments > 0 的总和来获取所有 FatherId。所以结果将是：

FatherId    |   Total   |
=========================
A           |     10    |
C           |     5     |

FatherId = B 未显示，因为 SUM(MovementType = IN) - SUM (MovementType = OUT) = 0

我试过了

SELECT FatherId,
  (SELECT (
    SUM(CASE WHEN MovementType = 'IN' THEN Quantity ELSE 0 END) -
    SUM(CASE WHEN MovementType = 'OUT' THEN Quantity ELSE 0 END)
  )) AS Total
  FROM MOVEMENTS
  GROUP BY FatherId
  ORDER BY FatherId

这给了我按父亲 ID 分组的结果，但我无法使用 Total > 0 进行过滤，而且我无法将此查询放在子查询中，例如：

SELECT * FROM MOVEMENTS WHERE FatherId IN (SELECT ....) OFFSET ... FETCH NEXT ... ROWS ONLY

这在没有存储过程的情况下可行吗？感谢您的任何帮助

标签： sqlsql-server

解决方案

为什么要使用子查询？这应该做你想要的：

SELECT FatherId,
       (SUM(CASE WHEN MovementType = 'IN' THEN Quantity ELSE 0 END) -
        SUM(CASE WHEN MovementType = 'OUT' THEN Quantity ELSE 0 END)
       ) AS Total
FROM MOVEMENTS
GROUP BY FatherId
HAVING (SUM(CASE WHEN MovementType = 'IN' THEN Quantity ELSE 0 END) -
        SUM(CASE WHEN MovementType = 'OUT' THEN Quantity ELSE 0 END)
       ) > 0;

您还可以简化逻辑以使用单个SUM()：

SELECT FatherId,
       SUM(CASE WHEN MovementType = 'IN' THEN Quantity
                WHEN MovementType = 'OUT' THEN - Quantity
                ELSE 0
           END) AS Total
FROM MOVEMENTS
GROUP BY FatherId
HAVING SUM(CASE WHEN MovementType = 'IN' THEN Quantity
                WHEN MovementType = 'OUT' THEN - Quantity
                ELSE 0
           END) > 0
ORDER BY FatherId;

推荐阅读