根据另一个表的小计更新字段

发布于 2024-07-12 05:49:56 字数 765 浏览 7 评论 0原文

我正在使用甲骨文（10）。

我有两个表，如下所示：

Table1 (uniq rows):
ID    AMOUNT     DATE 

Table2:
ID    AMOUNT1 AMOUNT2 ...AMOUNTN DATE

Table2 通过 ID 与 Table1 多对一连接。

我需要的是用以下内容更新 Table1.DATE：当通过 Table2.DATE 字段向后读取表 2 时，Table2 中的最后（最早）日期，其中 Table1.AMOUNT - SUM(Table2.AMOUNT1) <= 0。

有简单的方法吗？

提前致谢！

更新：正如我从你的回答中看到的，我有点错误地指定了这个问题。这里有一个详细的示例：

Table1 有：

ID: 1     AMOUNT:100    DATE:NULL

Table2 有（对于 ID：1，因此 ID 未在此处列出）：

AMOUNT1     DATE
50          20080131
30          20080121
25          20080111
20          20080101

所以在这种情况下，我需要 20080111 作为 Table1 中的 DATE 作为 50+30+ 25=> 100.

原文

I'm using oracle(10).

I've got two tables as follows:

Table1 (uniq rows):
ID    AMOUNT     DATE 

Table2:
ID    AMOUNT1 AMOUNT2 ...AMOUNTN DATE

Table2 is connected many to one to Table1 connected via ID.

What I need is update-ing Table1.DATE with: the last (earliest) date from Table2 where Table1.AMOUNT - SUM(Table2.AMOUNT1) <= 0, when reading table 2 backwards by the Table2.DATE field.

Is there a simple way to do it?

Thanks in advance!

UPDATE: as I see from your answers I had misspecified the question a bit. So here goes a detailed example:

Table1 has:

ID: 1     AMOUNT:100    DATE:NULL

Table2 has (for ID: 1 so ID is not listed in here):

AMOUNT1     DATE
50          20080131
30          20080121
25          20080111
20          20080101

So in this case I need 20080111 as the DATE in Table1 as 50+30+25 => 100.

分享到QQ

分享到微博

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

发布评论

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

盗心人 2024-07-19 05:49:56

根据您修改后的问题，这是使用分析函数的情况。

假设您的意思是 >=100 而不是 <= 100，如示例所示，并将列 DATE 重命名为 THEDATE，因为 DATE 是 Oracle 中的保留字：

update table1 set thedate=
( select max(thedate) from
  ( select id, thedate,
           sum(amount1) over (partition by id  order by thedate desc) cumsum
    from table2
  ) v
  where v.cumsum >= 100
  and v.id = table1.id
)

如果 100 表示 table1 的当前值，则将该行更改为：

  where v.cumsum >= table1.amount

Based on your revised question, this is a case for using analytic functions.

Assuming you meant >=100 rather than <= 100 as your example implies, and renaming columns DATE to THEDATE since DATE is a reserved word in Oracle:

update table1 set thedate=
( select max(thedate) from
  ( select id, thedate,
           sum(amount1) over (partition by id  order by thedate desc) cumsum
    from table2
  ) v
  where v.cumsum >= 100
  and v.id = table1.id
)

If the 100 means the current value of table1 then change that line to:

  where v.cumsum >= table1.amount

回复收藏 0 原文

独自唱情﹋歌 2024-07-19 05:49:56

首先 - 你的数据库布局感觉严重错误，但我想你不能/不想改变它。 Table1 可能应该是一个视图，而 Table2 没有给人留下正确标准化的印象。像 (ID, AMOUNT_TYPE, AMOUNT_VALUE, DATE) 这样的东西对我来说更有意义。

但要解决你的问题（这是T-SQL“UPDATE FROM”语法，但我认为Oracle知道它）：

UPDATE 
  Table1
SET
  Date = Table2Aggregate.MinDate
FROM
  Table1 
  INNER JOIN (
    SELECT Id, SUM(Amount1) SumAmount1, MIN(Date) MinDate 
    FROM Table2 
    GROUP BY Id
  ) AS Table2Aggregate ON Table1.Id = Table2Aggregate.ID 
WHERE
  Table1.Amount - Table2Aggregate.SumAmount1 <= 0

First off - your database layout feels severely wrong, but I guess you can't / don't want to change it. Table1 should probably be a view, and Table2 does not make the impression of proper normalization. Something like (ID, AMOUNT_TYPE, AMOUNT_VALUE, DATE) would make much more sense to me.

But to solve your problem (this is T-SQL "UPDATE FROM" syntax, but I think Oracle knows it):

UPDATE 
  Table1
SET
  Date = Table2Aggregate.MinDate
FROM
  Table1 
  INNER JOIN (
    SELECT Id, SUM(Amount1) SumAmount1, MIN(Date) MinDate 
    FROM Table2 
    GROUP BY Id
  ) AS Table2Aggregate ON Table1.Id = Table2Aggregate.ID 
WHERE
  Table1.Amount - Table2Aggregate.SumAmount1 <= 0

回复收藏 0 原文

~没有更多了~