累积减法导致Oracle的成功行

发布于 2025-02-13 05:07:54 字数 721 浏览 0 评论 0原文

我想找到两列的累积减法，并在表的后排行中显示结果（行是按顺序排序的）。
表1

ID | Item | Qty1 | Qty2 | Date 
1  | I1   | 10   | 100  | 5-Jul
1  | I1   | 20   | 90   | 6-Jul
1  | I1   | 15   | 70   | 7-Jul 
2  | I2   | 10   | 50   | 5-Jul 
2  | I2   | 50   | 40   | 6-Jul 
2  | I2   | 10   | -10  | 7-Jul

ID，项目是主要键
对于每个ID，项目组合qty2中的第一个值

对于所有随后的行QTY2-QTY1结果，应在下一行中显示直到最后一个记录。
不需要最后一排的减法结果

这是主要问题。

我还有一个其他问题 - ID的行，项目组合是从其他表（表2）中填充的，具有相同ID，项目组合的数量。在此排序表中，只能将该数量填充到第一行中吗？

table2

| ID | Item | Qty | 
| 1  | I1   |100  |
| 2  | I2   |50   |

这些QTY仅应复制到第一行ID，Table1的项目

都可以帮助您提供这些查询。

提前致谢！

原文

I want to find cumulative subtraction of the two columns and display the result in the succeeding row of the tables( rows are in a sorted order).
Table 1

ID | Item | Qty1 | Qty2 | Date 
1  | I1   | 10   | 100  | 5-Jul
1  | I1   | 20   | 90   | 6-Jul
1  | I1   | 15   | 70   | 7-Jul 
2  | I2   | 10   | 50   | 5-Jul 
2  | I2   | 50   | 40   | 6-Jul 
2  | I2   | 10   | -10  | 7-Jul

ID,Item is the Primary key
For each ID, Item combination the first value in Qty2 is populated
For all the succeeding rows Qty2-Qty1 result should be displayed in the next row till the last record.
The last row's subtraction result is not required

This is the main question.

I have an additional question-
the row1 of ID,Item combination is populated from a different table(Table2) having the qty for the same ID,Item combination. Any ways of populating that qty only to the first row in this sorted table?

Table2

| ID | Item | Qty | 
| 1  | I1   |100  |
| 2  | I2   |50   |

These qtys should be copied only to the first row of ID, Item of Table1

Could anyone please help with these queries.

Thanks in Advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夏日落 2025-02-20 05:07:54

对于第一部分，您可以使用以下类似。

with data (ID,Item,Qty1,Qty2,Date1 ) as
(select 1,'I1',10,100,'5-Jul' from dual union all
select 1,'I1',20,90,'6-Jul' from dual union all
select 1,'I1',15,70,'7-Jul' from dual union all
select 2,'I2',10,50,'5-Jul' from dual union all
select 2,'I2',50,40,'6-Jul' from dual union all
select 2,'I2',10,-10,'7-Jul' from dual)
select d.*, 
case when date1=min(date1) over (partition by id,item) then qty2
else Qty2 -  Qty1 end diff_qty
from data d;

请参阅小提琴

对于第二部分，请添加一个带有数据和样本输出的示例，因为它不太清楚。

For the first part you can use something like below.

with data (ID,Item,Qty1,Qty2,Date1 ) as
(select 1,'I1',10,100,'5-Jul' from dual union all
select 1,'I1',20,90,'6-Jul' from dual union all
select 1,'I1',15,70,'7-Jul' from dual union all
select 2,'I2',10,50,'5-Jul' from dual union all
select 2,'I2',50,40,'6-Jul' from dual union all
select 2,'I2',10,-10,'7-Jul' from dual)
select d.*, 
case when date1=min(date1) over (partition by id,item) then qty2
else Qty2 -  Qty1 end diff_qty
from data d;

Refer fiddle here.

For second part, please add an example with data and sample output, as its not very clear.

回复收藏 0 原文

~没有更多了~