时间戳

首页 > 解决方案 > SQL Server 收集最新事务并忽略已选择的事务

sql - SQL Server 收集最新事务并忽略已选择的事务

问题描述

时不时地向我展示一个起初看起来简单直接的场景，但经过进一步审查，它的复杂性超出了我的范围。

我有 anvils 和 anvil_ledger 交易。我正在尝试创建一个批处理过程来获取最新的铁砧交易以获取每日报告。

现在一个铁砧可能有两个分类帐交易，我想得到最新的一个。

这是数据和代码...

drop table #anvil
drop table #anvil_ledger
drop table #anvil_batch

create  table #anvil(anvil_guid uniqueidentifier constraint anvil_guid_pk  primary key nonclustered
, anvil_status varchar(10)
, balance money
, utcinserted datetime constraint anvil_utcinserted_df default getutcdate()
)

create table #anvil_ledger(anvil_ledger_guid uniqueidentifier not null, post_date datetime not null, anvil_guid uniqueidentifier, action varchar(10) not null, amount money, utcinserted datetime constraint anvil_ledger_utcinserted_df default getutcdate())
alter table #anvil_ledger add constraint anvil_ledger_anvil_ledger_guid_post_date_df primary key (anvil_ledger_guid, post_date)

create table #anvil_batch(anvil_batch_guid uniqueidentifier, anvil_batch_date datetime,  anvil_guid uniqueidentifier, anvil_ledger_guid uniqueidentifier, post_date datetime, action varchar(10), utcinserted datetime constraint anvil_batch_guid_utcinserted_df default getutcdate())

insert into #anvil(anvil_guid, balance, anvil_status) values('3791A9C1-8697-4E12-8B0C-1D970ED90729',  1.00, 'Open')
insert into #anvil(anvil_guid, balance, anvil_status) values('A8AFCDD9-FD10-4443-B6AF-7FB30A260E29',  2.00, 'Open')
insert into #anvil(anvil_guid, balance, anvil_status) values('03C8B5AD-75E3-4BF4-B0CE-292FC2993F9C',  3.00, 'Open')
insert into #anvil(anvil_guid, balance, anvil_status) values('B280904E-D454-4343-911B-FEA85DEE478E',  4.00, 'Open')
insert into #anvil(anvil_guid, balance, anvil_status) values('5DAEFAAB-8432-4AA6-A616-FC4962279829',  0.00, 'Closed')
insert into #anvil(anvil_guid, balance, anvil_status) values('750EDD08-C76E-4AE5-A489-1035F889D576',  0.00, 'Closed')
insert into #anvil(anvil_guid, balance, anvil_status) values('051D7A55-CCAB-4F20-82F7-799C1710CC2B',  7.00, 'Open')
insert into #anvil(anvil_guid, balance, anvil_status) values('46E97CA7-8DBB-4D13-B123-614E180EF3AD',  8.00, 'Open')
insert into #anvil(anvil_guid, balance, anvil_status) values('6EC2DCBF-A6ED-450E-9398-F86BC6DD3786',  9.00, 'Open')
insert into #anvil(anvil_guid, balance, anvil_status) values('12C8F5AA-31B9-4E5C-981A-4C246BF86DA1', 10.00, 'Open')


insert into #anvil_ledger(anvil_ledger_guid, post_date, anvil_guid, action, amount) values('55287267-40DD-48BC-A4F5-F2F8E9344813', '2021-02-26 01:00:00.000', '3791A9C1-8697-4E12-8B0C-1D970ED90729', 'New', 1.00)
insert into #anvil_ledger(anvil_ledger_guid, post_date, anvil_guid, action, amount) values('7C56E350-EAD8-468C-82AF-05A858686776', '2021-02-26 01:00:00.000', 'A8AFCDD9-FD10-4443-B6AF-7FB30A260E29', 'New',  2.00)
insert into #anvil_ledger(anvil_ledger_guid, post_date, anvil_guid, action, amount) values('D17081C7-E965-4C8D-9275-1982B301FE45', '2021-02-26 02:00:00.000', '03C8B5AD-75E3-4BF4-B0CE-292FC2993F9C', 'New', 3.00)
insert into #anvil_ledger(anvil_ledger_guid, post_date, anvil_guid, action, amount) values('F9EF78FA-6517-49FA-98FF-0D2739D5026D', '2021-02-26 03:00:00.000', 'B280904E-D454-4343-911B-FEA85DEE478E', 'New', 4.00)
insert into #anvil_ledger(anvil_ledger_guid, post_date, anvil_guid, action, amount) values('848D6796-FE7D-4908-A0F4-8B14A0FA4DE2', '2021-02-26 04:00:00.000', '5DAEFAAB-8432-4AA6-A616-FC4962279829', 'New', 5.00)
insert into #anvil_ledger(anvil_ledger_guid, post_date, anvil_guid, action, amount) values('D0C79E19-F7A1-4CF9-BDC8-821EAD390586', '2021-02-26 05:00:00.000', '750EDD08-C76E-4AE5-A489-1035F889D576', 'New', 6.00)
insert into #anvil_ledger(anvil_ledger_guid, post_date, anvil_guid, action, amount) values('30AF2C73-0418-4D65-BE62-C2992CC50DE2', '2021-02-26 06:00:00.000', '051D7A55-CCAB-4F20-82F7-799C1710CC2B', 'New', 7.00)
insert into #anvil_ledger(anvil_ledger_guid, post_date, anvil_guid, action, amount) values('AA9996D8-1073-4660-97E4-C8AB92E31667', '2021-02-26 07:00:00.000', '46E97CA7-8DBB-4D13-B123-614E180EF3AD', 'New', 8.00)
insert into #anvil_ledger(anvil_ledger_guid, post_date, anvil_guid, action, amount) values('E9065F4E-8D92-4111-AAD5-800CB0B723B0', '2021-02-26 08:00:00.000', '6EC2DCBF-A6ED-450E-9398-F86BC6DD3786', 'New', 9.00)
insert into #anvil_ledger(anvil_ledger_guid, post_date, anvil_guid, action, amount) values('F2A9D713-B38C-4068-9AC7-68B872FFD8AA', '2021-02-26 09:00:00.000', '12C8F5AA-31B9-4E5C-981A-4C246BF86DA1', 'New', 10.00)
insert into #anvil_ledger(anvil_ledger_guid, post_date, anvil_guid, action, amount) values('FA81B21E-B116-4F51-984B-59C7A5B3AC0C', '2021-02-27 04:00:00.000', '5DAEFAAB-8432-4AA6-A616-FC4962279829', 'Cancel', -5.00)
insert into #anvil_ledger(anvil_ledger_guid, post_date, anvil_guid, action, amount) values('655ACAD0-B4B7-4E6F-BCF1-C19DE415908E', '2021-02-27 05:00:00.000', '750EDD08-C76E-4AE5-A489-1035F889D576', 'Cancel', -6.00)


declare @anvil_batch_guid uniqueidentifier = 'ABE64AC0-7448-4A03-9A0D-E5D67C9585DC'
, @anvil_batch_date datetime = '2021-02-27 10:00:00.000'


insert into #anvil_batch(anvil_batch_guid, anvil_batch_date, anvil_guid, anvil_ledger_guid, post_date, action)
select @anvil_batch_guid, @anvil_batch_date, vt.anvil_guid, vt.anvil_ledger_guid, vt.post_date, vt.action
from(
select al.post_date, al.anvil_ledger_guid, al.anvil_guid, al.action, al.amount, a.balance, a.anvil_status, row_number() over(partition by a.anvil_guid order by al.post_date desc) as rn
from #anvil_ledger al 
inner join #anvil a on a.anvil_guid = al.anvil_guid
) vt
where rn = 1

到目前为止，这有效。我能够获得最新的 anvil_ledger 交易。记录被插入到#anvil_batch。

现在这里是不是它变得棘手。我想每天运行它，当然我不想包含已经收集的 anvil_ledger 交易。我最初的想法是离开加入到 anvil_batch 并过滤不为空的记录......

select  vt.anvil_guid, vt.anvil_ledger_guid, vt.post_date, vt.balance, vt.action
from(
select al.post_date, al.anvil_ledger_guid, al.anvil_guid, al.action, al.amount, a.balance, a.anvil_status, row_number() over(partition by a.anvil_guid order by al.post_date desc) as rn
from #anvil_ledger al 
inner join #anvil a on a.anvil_guid = al.anvil_guid
left join #anvil_batch ab on ab.post_date = al.post_date and ab.anvil_guid = al.anvil_guid
where ab.anvil_guid is null
) vt
where rn = 1

这会返回两条我不想要的记录...

anvil_guid                           anvil_ledger_guid                    post_date               balance               action
------------------------------------ ------------------------------------ ----------------------- --------------------- ----------
750EDD08-C76E-4AE5-A489-1035F889D576 D0C79E19-F7A1-4CF9-BDC8-821EAD390586 2021-02-26 05:00:00.000 0.00                  New
5DAEFAAB-8432-4AA6-A616-FC4962279829 848D6796-FE7D-4908-A0F4-8B14A0FA4DE2 2021-02-26 04:00:00.000 0.00                  New

我希望此查询不返回任何记录，因为我已经在 anvil_batch 中有最新记录。

现在，如果插入了更新的 anvil_ledger 交易，那么我希望选择更新的交易。

任何帮助将不胜感激。

标签： sqlsql-server-2016

解决方案

您的窗口功能在错误的位置。我会建议：

select al.*
from (select al.*,
             row_number() over (partition by al.anvil_guid order by al.post_date desc) as seqnum
      from #anvil_ledger al 
     ) al left join
     #anvil_batch ab
     on ab.post_date = al.post_date and ab.anvil_guid = al.anvil_guid
where seqnum = 1 and ab.anvil_guid is null;

我省略了这#anvil部分，因为我认为它只是混淆了逻辑的重要部分（你当然可以包括它）。

您的查询失败，因为它在计算行号之前正在执行左连接和过滤。

推荐阅读