有人可以帮我解决这个问题吗？
注意：起初，我想为我的英语道歉...:)
描述
情况
我有生产记录表。 此表中是每个生产过程的处理量数据。
生产中的流程必须按时间顺序排列，例如：流程1→流程2→流程3→等，但产品的生产不必按时间顺序排列。
问题
我想对某个进程(进程-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
我试过了。。。
使用自加入
(按产品分组(
SELECT SUM(A.Amount)-SUM(B.Amount) FROM Data A, Data B
WHERE A.Process = 1 AND B.Process = 2
GROUP BY A.Product
它看起来不错，但结果是错误的...
使用子选择 #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)
它有效，但仅适用于产品1(可能是因为它在数据表中排在第一位(。
子选择的使用 #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'
这得到的结果与使用子选择 #1中的结果相同...
非常感谢
因为某些产品可能在某些过程中，也可能不在某个过程中，首先我们必须收集所有有效的产品，然后才计算结果 这是我刚刚对您的数据测试的代码：
with all_prod as (select distinct Product from t1)
select
ap.Product, coalesce(B.Amount,0) - coalesce(A.Amount,0) Amount
from all_prod ap
left join (
select SUM(Amount) Amount, Product, 2 Process
from t1
where Process = 2
group by Product, Process) A on A.Product = ap.Product
left join (
select SUM(Amount) Amount, Product, 2 Process
from t1
where Process = 1
group by Product, Process) B on ap.Product = B.Product
它计算进程 2 的值，要获得进程 3 的结果，您应该更改where子句中的值
您可以使用它，将编号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;