用最旧的值更新第一行，用第二旧的值更新下一行，依此类推

发布于 2024-12-16 12:25:11 字数 1330 浏览 4 评论 0原文

我在没有关系的 SQL Server 中编写更新时遇到问题。我浏览了整个论坛，但很难找到答案。

如果客户、金额匹配，我需要更新 OCR。问题是这不是唯一的密钥。因此，如果来自客户、金额组合的记录超过一条，我需要获取查找表中最旧的匹配项并从中更新 OCR。然后我需要取出第二旧的并用其 OCR 更新第二行。

我试图在下表中形象化它。

欢迎所有建议！

要更新的表 - 更新前

Customer       OCR  Amount      
740000010417        220.000     
740000010417        220.000     
740000010421        300.000     
740000010421        250.000

查找表

Customer         OCR            Amount  Date                    ID
740000010417    222357110626    220.000 2011-11-11 15:48:48.510 100642
740000010417    222350553822    220.000 2011-10-18 10:10:26.210 18680
740000010417    222350464525    220.000 2011-10-18 10:10:26.210 18681
740000010417    222357110725    220.000 2011-11-11 15:48:48.510 102547
740000010421    222357127726    250.000 2011-11-11 15:48:48.510 102548
740000010421    222357127725    220.000 2011-10-19 10:10:26.210 102549
740000010421    222357130555    250.000 2011-10-19 10:10:26.210 102550

更新后的表

Customer            OCR          Amount     
740000010417    222350553822    220.000     
740000010417    222350464525    220.000     
740000010421                    300.000     
740000010421    222357130555    250.000

原文

I'm having trouble writing an update in SQL Server with no relations. I have looked all over the forum but I have a hard time to find the answer.

I need to update the OCR from if the Customer, Amount is matching. The problem is that this is not a unique key. So, if there is more then one record from the customer, amount combination, I need to take the oldest match in the lookup table and update the OCR from it. Then I need to take the second oldest and update the second row with its OCR.

I tried to visualize it in the tables below.

All suggestions are welcome!

Table to update - before update

Customer       OCR  Amount      
740000010417        220.000     
740000010417        220.000     
740000010421        300.000     
740000010421        250.000

Lookup Table

Customer         OCR            Amount  Date                    ID
740000010417    222357110626    220.000 2011-11-11 15:48:48.510 100642
740000010417    222350553822    220.000 2011-10-18 10:10:26.210 18680
740000010417    222350464525    220.000 2011-10-18 10:10:26.210 18681
740000010417    222357110725    220.000 2011-11-11 15:48:48.510 102547
740000010421    222357127726    250.000 2011-11-11 15:48:48.510 102548
740000010421    222357127725    220.000 2011-10-19 10:10:26.210 102549
740000010421    222357130555    250.000 2011-10-19 10:10:26.210 102550

Table after update

Customer            OCR          Amount     
740000010417    222350553822    220.000     
740000010417    222350464525    220.000     
740000010421                    300.000     
740000010421    222357130555    250.000

分享到QQ

分享到微博

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

发布评论

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

把人绕傻吧 2024-12-23 12:25:11

update table set ocr = 

(select l.ocr
from 
(select l.customer as customer, l.ocr as ocr, l.amount as amount, l.date as date, ROW_NUMBER() OVER (partition by l.customer, l.amount Order BY l.date) as RowNum
from lookuptable l
order by l.date
)a

(select t.customer as customer, t.amount as amount, ROW_NUMBER() OVER (PARTITION BY t.customer, t.amount order by t.customer) as RowNum
from table t
)b
where a.customer = b.customer and a.amount=b.amount and a. rowNum = b.RowNum
)

我还没有测试过它，但它可能会给你一个想法。

编辑：
刚刚意识到不需要加入内部查询。
想法是首先从查找表中选择所有记录，并按日期升序为它们分配行号。
因此，相同的客户和不同日期的相同金额将获得增量订单中的行号。

然后从旧表中获取记录并为其分配按客户和金额分区的行号。这样我们就可以匹配客户、金额和行号，这样第一个相同的客户和金额将被初始化为最旧的 OCR，因为行按日期排序

update table set ocr = 

(select l.ocr
from 
(select l.customer as customer, l.ocr as ocr, l.amount as amount, l.date as date, ROW_NUMBER() OVER (partition by l.customer, l.amount Order BY l.date) as RowNum
from lookuptable l
order by l.date
)a

(select t.customer as customer, t.amount as amount, ROW_NUMBER() OVER (PARTITION BY t.customer, t.amount order by t.customer) as RowNum
from table t
)b
where a.customer = b.customer and a.amount=b.amount and a. rowNum = b.RowNum
)

I have not tested it, but its might give you an idea.

Edit:
just realized no need to join in inner query.
Idea is to first select all the records from look up table and assign them row number in ascending order of date.
so same customer and same amount with different dates will get row numbers in increment orders.

Then get records from old table and assign row numbers to them partiioned by customer and amount. This way we can match customer, amount and row number sunch that first same customer and amount will be initialized oldest OCR, as rows are ordered by date

回复收藏 0 原文

埋葬我深情 2024-12-23 12:25:11

这就是我最终得到的结果，效果非常好！谢谢佐海卜！

UPDATE   t1
SET    t1.ocr = l1.ocr    
FROM    ( SELECT    *
              , rnk = ROW_NUMBER() OVER ( PARTITION BY t.customer,
                                          t.Amount ORDER BY t.customer, t.Amount )
           FROM      table t) t1
        LEFT JOIN 
    ( SELECT    *
              , rnk = ROW_NUMBER() OVER ( PARTITION BY l.customer,
                                          l.Amount ORDER BY l.date, l.id)
          FROM      lookuptable l) l1

      ON t1.id = l1.id
        AND t1.Amount = l1.amount
        AND t1.rnk = l1.rnk

This is what I ended up with that worked like a charm! Thanks Zohaib!

UPDATE   t1
SET    t1.ocr = l1.ocr    
FROM    ( SELECT    *
              , rnk = ROW_NUMBER() OVER ( PARTITION BY t.customer,
                                          t.Amount ORDER BY t.customer, t.Amount )
           FROM      table t) t1
        LEFT JOIN 
    ( SELECT    *
              , rnk = ROW_NUMBER() OVER ( PARTITION BY l.customer,
                                          l.Amount ORDER BY l.date, l.id)
          FROM      lookuptable l) l1

      ON t1.id = l1.id
        AND t1.Amount = l1.amount
        AND t1.rnk = l1.rnk

回复收藏 0 原文

~没有更多了~