时间戳

首页 > 解决方案 > 第一列（金额）中的值的减法总和由另一个第二列（产品）分组，条件在另一个第三列（过程）

sql - 第一列（金额）中的值的减法总和由另一个第二列（产品）分组，条件在另一个第三列（过程）

问题描述

有人可以帮我解决这个问题吗？

注意：首先，我想为我的英语道歉...... :)

描述

情况

我有生产记录表。此表中是每个生产过程的加工量数据。

生产过程必须按时间顺序排列，如：过程- 1 →过程- 2 →过程- 3 → 等等，但产品的生产不必按时间顺序排列。

问题

我想对某个流程（除Process - 1之外的任何流程）使用查询，并且我想知道每个产品有多少数量可用于此流程。

我的意思是：我想知道上一个过程中的加工产品数量减去当前过程中加工产品的数量。

例子

数据表

生产： | DataID | Product | Amount | Process | |--------|---------|--------|---------| | 1 | P1 | 50 | 1 | | 2 | P2 | 40 | 1 | | 3 | P1 | 25 | 1 | | 4 | P1 | 60 | 2 | | 5 | P2 | 20 | 1 | | 6 | P3 | 75 | 1 | | 7 | P2 | 30 | 2 | | 8 | P1 | 35 | 3 | | 9 | P2 | 10 | 3 | | 10 | P3 | 50 | 2 |

要求

查询进程 2

过程2： | Process | Product | Amount | |---------|---------|--------| | 2 | P1 | 15 | | 2 | P2 | 30 | | 2 | P3 | 25 |

因为：

P1 = 50 + 25 - 60 = 15

P2 = 40 + 20 - 30 = 30

P3 = 75 - 50 = 25

查询进程 3

过程3： | Process | Product | Amount | |---------|---------|--------| | 3 | P1 | 25 | | 3 | P2 | 20 | | 3 | P3 | 50 |

因为：

P1 = 60 - 35 = 25

P2 = 30 - 10 = 20

P3 = 50 = 50

我试过了...

SELF JOIN 的使用

（按产品分组）

SELECT SUM(A.Amount)-SUM(B.Amount) FROM Data A, Data B
WHERE A.Process = 1 AND B.Process = 2
GROUP BY A.Product

它看起来不错，但结果是错误的......

使用 SubSelects #1

SELECT (X - Y) AS 'Amount' FROM
(SELECT SUM(Amount) AS X FROM Data WHERE Process = 1),
(SELECT SUM(Amount) AS Y FROM Data WHERE Process = 2)

它有效，但仅适用于Product- 1（可能是因为它在数据表中是第一个）。

使用 SubSelects #2

SELECT
((SELECT SUM(Amount) FROM Data WHERE Process = 1 GROUP BY Product) - 
(SELECT SUM(Amount) FROM Data WHERE Process = 2 GROUP BY Product)) AS 'Amount'

这与使用 SubSelects #1中的结果相同...

十分感谢

标签： sqlsql-server

解决方案

您可以使用它，将编号更改2为您的进程编号（替换 4 个位置）：

这项工作适用于 SQLServer 2005+

WITH total_in_process AS 
(
    SELECT product, process,
        SUM(amount) AS amount
    FROM table_name
    GROUP BY product, process
)
SELECT 2 AS process,
    t1.product AS product,
    COALESCE(t1.amount, 0) - COALESCE(t.amount, 0) AS amount
FROM total_in_process t1
LEFT JOIN total_in_process t
ON t1.product = t.product AND t1.process = t.process - 1
WHERE COALESCE(t.process, 2) = 2 AND t1.process = 2 - 1 
ORDER BY t1.product;

这是SQLFiddle

Edited：如果您有进程 4 并且您想从最后一个进程（进程 2/1 而不是 3）中获取剩余值，那么您应该使用此查询：

WITH all_product AS 
(
    SELECT DISTINCT product
    FROM table_name
)
, total_in_process AS 
(
    SELECT product, process,
        SUM(amount) AS amount
    FROM table_name
    GROUP BY product, process
)
, last_process AS 
(
    SELECT product, 
        MAX(process) AS last_process
    FROM total_in_process
    WHERE process < 4
    GROUP BY product
)
, last_process_value AS 
(
    SELECT t.product, t.process, t.amount
    FROM total_in_process t 
    INNER JOIN last_process l
    ON t.product = l.product AND t.process = l.last_process
)
SELECT 4 AS process,
    p.product,
    COALESCE(l.amount, 0) - COALESCE(t.amount, 0) AS amount
FROM all_product p
LEFT JOIN total_in_process t
ON p.product = t.product AND t.process = 4 
LEFT JOIN last_process_value l
ON l.product = p.product 
ORDER BY p.product;

推荐阅读