I have a postgresql table containing movements of different items (models) between warehouses.
我有一个postgresql表,其中包含仓库之间不同项目(模型)的移动。
For example, the following record means that 5 units of model 1 have been sent form warehouse 1 to 2:
例如,下面的记录表示模型1的5个单元已经从仓库1发送到仓库2:
source target model units
------ ------ ----- -----
1 2 1 5
I am trying to build a SQL query to obtain the difference between units sent and received, grouped by models. Again with an example:
我正在尝试构建一个SQL查询,以获得发送和接收单元之间的差异,并按模型分组。又用一个例子:
source target model units
------ ------ ----- -----
1 2 1 5 -- 5 sent from 1 to 2
1 2 2 1
2 1 1 2 -- 2 sent from 2 to 1
2 1 1 1 -- 1 more sent from 2 to 1
The result should be:
结果应该是:
source target model diff
------ ------ ----- ----
1 2 1 2 -- 5 sent minus 3 received
1 2 2 1
I wonder if this is possible with a single SQL query.
我想知道是否可以使用一个SQL查询。
Here is the table creation script and some data, just in case anyone wants to try it:
这里是表创建脚本和一些数据,以防有人想尝试:
CREATE TEMP TABLE movements
(
source INTEGER,
target INTEGER,
model INTEGER,
units INTEGER
);
insert into movements values (1,2,1,5);
insert into movements values (1,2,2,1);
insert into movements values (2,1,1,2);
insert into movements values (2,1,1,1);
2 个解决方案
#1
1
Does this do what you need? I don't have an Oracle DB to test against so I hope the rules on Grouping expressions are the same as for MS SQL Server
这能满足你的需要吗?我没有一个Oracle DB来测试,所以我希望对分组表达式的规则与MS SQL Server相同。
SELECT
CASE WHEN source < target THEN source ELSE target END AS source,
CASE WHEN source < target THEN target ELSE source END AS target,
SUM(CASE WHEN source < target THEN units ELSE -units END) AS Diff,
model
FROM movements
GROUP BY
CASE WHEN source < target THEN source ELSE target END,
CASE WHEN source < target THEN target ELSE source END,
model
#2
4
You can do this with two subselects which sum the movements in each direction, and then union and sum the results of those two subqueries:
你可以用两个子选择来完成这两个子选择,分别对每个方向的运动求和,然后结合两个子查询的结果:
SELECT source, target, model, SUM(units)
FROM (
SELECT source, target, model, SUM(units) AS units
FROM movements
WHERE source < target
GROUP BY source, target, model
UNION ALL
SELECT target, source, model, SUM(-units) AS units
FROM movements
WHERE source > target
GROUP BY source, target, model
) T1
GROUP BY source, target, model
#1
1
Does this do what you need? I don't have an Oracle DB to test against so I hope the rules on Grouping expressions are the same as for MS SQL Server
这能满足你的需要吗?我没有一个Oracle DB来测试,所以我希望对分组表达式的规则与MS SQL Server相同。
SELECT
CASE WHEN source < target THEN source ELSE target END AS source,
CASE WHEN source < target THEN target ELSE source END AS target,
SUM(CASE WHEN source < target THEN units ELSE -units END) AS Diff,
model
FROM movements
GROUP BY
CASE WHEN source < target THEN source ELSE target END,
CASE WHEN source < target THEN target ELSE source END,
model
#2
4
You can do this with two subselects which sum the movements in each direction, and then union and sum the results of those two subqueries:
你可以用两个子选择来完成这两个子选择,分别对每个方向的运动求和,然后结合两个子查询的结果:
SELECT source, target, model, SUM(units)
FROM (
SELECT source, target, model, SUM(units) AS units
FROM movements
WHERE source < target
GROUP BY source, target, model
UNION ALL
SELECT target, source, model, SUM(-units) AS units
FROM movements
WHERE source > target
GROUP BY source, target, model
) T1
GROUP BY source, target, model