使用最后一行字段值更新字段

发布于 2025-02-10 19:27:04 字数 1992 浏览 0 评论 0原文

下

客户	数量	日期	lastmonthdate	sumlastmonthamount
1	500	20220301	20220201500	1
200	转	20220304	202204	700
1	400	20220320220220220220220220220220220220220220220220220220220220220220220220220220220220220220220220200	我	假设
：	202204	表	有

上个月;假设SumlastMonth是null在表中，我应该如何更新此列？

请注意，您根本不能使用日期功能。当您看到

我为此任务写的查询时，我们的日期列具有INT类型：

UPDATE A
SET SumLastMonthAmount = (SELECT SUM(Amount) 
                          FROM Table B 
                          WHERE A.CustomerId = B.CustomerId
                            AND B.Date > A.LastMonthDate
                            AND B.Date <= A.Date)                          
FROM Table A
Where A.Date=20220402

但是它非常慢。您可以建议一个更好的查询吗？

原文

Suppose I have the following table:

CustomerId	Amount	Date	LastMonthDate	SumLastMonthAmount
1	500	20220301	20220201	500
1	200	20220304	20220204	700
1	400	20220320	20220220	1100
1	100	20220329	20220229	1200
1	100	20220402	20220302	800

As you can see, I want to have sum of amount for last month; suppose that SumLastMonth is NULL in the table, how should I update this column?

Note that you can't use date functions at all. and our date columns have int type as you see

The query that I wrote for this task is:

UPDATE A
SET SumLastMonthAmount = (SELECT SUM(Amount) 
                          FROM Table B 
                          WHERE A.CustomerId = B.CustomerId
                            AND B.Date > A.LastMonthDate
                            AND B.Date <= A.Date)                          
FROM Table A
Where A.Date=20220402

But it is very slow. Can you suggest a better query?

分享到QQ

分享到微博

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

发布评论

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

情魔剑神 2025-02-17 19:27:04

您的代码被修改如下。如果未确定上个月的“上个月”金额为null

with cte as(
    select  A.CustomerId , A.LastMonthDate, A.Date,SUM(B.Amount) as newLastMonthAmount 
    from Table A 
    left Join Table B  
    on A.CustomerId = B.CustomerId 
    AND B.Date > A.LastMonthDate 
    AND B.Date <= A.Date 
    group by A.CustomerId , A.LastMonthDate, A.Date
) 
update A 
set SumLastMonthAmount=B.newLastMonthAmount 
from Table A 
join cte B 
on  A.CustomerId = B.CustomerId 
AND B.Date = A.Date 
AND B.LastMonthDate = A.LastMonthDate

your code is modified as below. if last month is not identified "last Month amount" will be null

with cte as(
    select  A.CustomerId , A.LastMonthDate, A.Date,SUM(B.Amount) as newLastMonthAmount 
    from Table A 
    left Join Table B  
    on A.CustomerId = B.CustomerId 
    AND B.Date > A.LastMonthDate 
    AND B.Date <= A.Date 
    group by A.CustomerId , A.LastMonthDate, A.Date
) 
update A 
set SumLastMonthAmount=B.newLastMonthAmount 
from Table A 
join cte B 
on  A.CustomerId = B.CustomerId 
AND B.Date = A.Date 
AND B.LastMonthDate = A.LastMonthDate

回复收藏 0 原文

~没有更多了~