更加优雅的基于复合标准更新表格的方法 [英] more elegant way of updating a table based on compounded criteria
问题描述
您好我们运行2017年企业。
我们有一个类似于代码块中显示的表格。 在t-sql while循环中，我想设置名为CompositeKey的列，以便任何与任何其他行（直接或间接）相关的行共享相同的CompositeKey。
让我说我有3个应用程序，会计，营销和运营。 每个应用程序都是从第三方供应商处购买的，该供应商拥有自己的员工ID值。 因此，John Doe在会计方面的员工ID是AX。 他在营销中的身份
是我的。 在运营中它的OZ。 我希望我的解决方案可以扩展到n个应用程序。 翻译表（2-n外部参照）提供此信息。 所有翻译表都同样重要。 因此，我们通过将
的内容减少到对，然后以维护合法关系的方式重新加入数据来清理它们所包含的内容。 之后我们还会在进行额外的错误检查后将复合键分配给孤儿，因为所有的键都必须在最终结果中表示。
所以，在我的表#dedupe中我有3条记录对于John Doe ...
申请1 密钥1 申请2 Key2
会计 AX            市场营销与NBSP;             
MY
会计 AX            操作             
OZ
营销 MY           操作             
OZ
它并不总是这么干净。 使用更多应用可能会变得更加复杂。 关系可能会丢失等。
所以目前，我的算法编码如第二个代码块所示。
是否有更优雅的方式来编写第二次更新 声明？ 该算法的一般思想是内部在外部消耗所有可能的传递排列，同时访问表中的下一个候选（未标记）
记录。 我不确定我是否喜欢在该更新语句中使用2个左连接而不是更优雅的存在，交叉等基于语句。
------- -------------------------------------------------- -------------------------------------------------- -----------
- 将n个翻译源的关键映射缩减为密钥对的排列，以便更容易管理。
- 重复一次加载到重复数据删除临时表中的对
----------------------------- -------------------------------------------------- ---------------------------------------
创建表#Dedupe（DedupeId int identity（1,1），
TranslationTable varchar（250），
Application1 varchar（250），
Application2 varchar（250），
Key1 varchar（250）， - < ; ------------------------------------ guids将自动投射
Key2 varchar（250） ， - < ------------------------------------ guids将自动投射
[动作] char（1），
CompositeKey varchar（10））
-------------- -------------------------------------------------- -------------------------------------------------- ------
- 我们此时必须重新加入相关记录。从我们已经回归到
中的完全关系的配对开始 - 传递方式需要特殊处理，因为存在空值的可能性。每个记录都被访问
- 在下一个块中，所有可能的关系都用完了，然后我们继续下一个记录。由于此业务问题具有传递性，因此必须在此块运行后进行最终
- 错误检查。
---------------------------------------------- -------------------------------------------------- ------------------------
set @ dedupeSearch1 = 1 - < ------------- -----------------------用这个来访问每个重复数据删除记录
而@ dedupeSearch1< = @countDedupes
开始
选择@ Application1 = Application1，@ Application2 = Application2，@ Key1 = Key1，@ Key2 = Key2，@ Action = [Action]，@ CompositeKey = CompositeKey
来自#Dedupe
其中DedupeId = @ dedupeSearch1
----------------------------------------------- ---------------------------
- 仅处理非排除，非差异和非分配的重复数据删除记录
- -------------------------------------------------- ----------------------
如果@Action =''和@Compositekey =''
开始
--- -------------------------------------------------- -------------------------------------------------- -----
- 新关系的新复合键onships是必要的（这只是内部，外部将是一个seq guid）
------------------------------ -------------------------------------------------- ----------------------------
set @composite = @composite + 1
update #dedupe set CompositeKey = @composite其中DedupeId = @ dedupeSearch1
设置@updateCount = 1
而@updateCount> 0
开始
更新a
设置a.CompositeKey = @composite
来自#dedupe a
--------------- -------------------------------------------------- -------------------------------
- 寻找更优雅的方式，也许将b和c转换为1临时表，也许存在并交叉
--------------------------------------- -------------------------------------------------- -------
左加入
（
选择Application1，来自#dedupe的Key1，其中CompositeKey = @composite和（coalesce（Key1，''）<>''）
union all
选择Application2，Key2来自#dedupe，其中CompositeKey = @composite和（coalesce（Key2，''）<>''）
）b
on a .Application1 = b.Application1和a.Key1 = b.key1
左连接
（
选择Application1，Key1来自#dedupe，其中CompositeKey = @composite和（合并（Key1，' '）<>''）
union all
选择Application2，来自#dedupe的Key2，其中CompositeKey = @composite和（coalesce（Key2，''）<>''）
）a.Application2 = c.Application1上的c
和a.Key2 = c.key1
其中a。[Action] =''和
a.CompositeKey =''和
（（coalesce（b.key1，''）<>''）或（coalesce（c.key1，''）<>''））
set @updateCount = @@ RowCount
结束
结束
设置@ dedupeSearch1 = @ dedupeSearch1 + 1
结束
Hi
db042190 ，
你想要一个吗？
在下面的脚本中，我将LEFT JOIN替换为exists，你可以尝试看看是否满足你的要求。顺便问一下，你为什么要这样做？使用LEFT JOIN？当你使用
LEFT JOIN时，左表将显示 完成 ly在你的脚本中它似乎毫无意义。
set @ dedupeSearch1 = 1 - < ------------ ------------------------用这个来访问每个重复数据删除记录
而@ dedupeSearch1< = @countDedupes
开始
select @ Application1 = Application1，@ Application2 = Application2，@ Key1 = Key1，@ Key2 = Key2，@ Action = [Action]，@ CompositeKey = CompositeKey
来自#Dedupe
其中DedupeId = @ dedupeSearch1
---------------------------------------------- ----------------------------
- 仅处理非排除，非差异和非分配的重复数据删除记录
- -------------------------------------------------- -----------------------
如果@Action =''和@Compositekey =''
开始
- -------------------------------------------------- -------------------------------------------------- ------
- 新关系的新复合关键是必要的（这只是内部的，外部的将是一个seq guid）
------------------------------------------- -------------------------------------------------- ---------------
set @composite = @composite + 1
update #dedupe set CompositeKey = @composite其中DedupeId = @ dedupeSearch1
set @ updateCount = 1
而@updateCount> 0
开始
; cte为（
选择Application1，来自#dedupe的Key1，其中CompositeKey = @composite和（coalesce（Key1，''）<>''）
union all
选择Application2，Key2来自#dedupe，其中CompositeKey = @composite和（coalesce（Key2，''）<>''）
）
更新
从#dedupe a
设置a.CompositeKey = @composite
------------------------------- -------------------------------------------------- ---------------
- 寻找更优雅的方式，也许将b和c转储到1个临时表中，也许存在并交叉
----- -------------------------------------------------- -----------------------------------------
where（exists（select。） *来自cte b，其中a.Application1 = b.Application1和a.Key1 = b.key1和（coalesce（b.key1，''）<>''）
或存在（select * from cte c其中a.Ap plication2 = c.Application1和a.Key2 = c.key1和（coalesce（c.key1，''）<>''））
和a。[Action] =''和a.CompositeKey = ''
设置@updateCount = @@ RowCount
结束
结束
设置@ dedupeSearch1 = @ dedupeSearch1 + 1
结束
希望它可以帮到你。
最好的问候，
Rachel
&NBSP;
Hi we run 2017 enterprise.
We have a table that looks like the one shown in the code block. In a t-sql while loop I want to set the column called CompositeKey such that any row that is related to any other row (directly or indirectly) shares the same CompositeKey.
Lets say I have 3 applications, Accounting, Marketing and Operations. And each application is purchased from a 3rd party vendor that has its own values for employee id's. So John Doe's employee id in Accounting is AX. His id in Marketing is MY. And in Operations its OZ. I want my solution to be scalable to n applications. Translation tables (2-n xrefs) source this info. All translation tables are equally important. So we are cleansing what they contain by reducing their content to pairs and then rejoining the data in a way that maintains legitimate relationships. Later we would also assign composite keys to orphans after an additional error check is made because all keys have to be represented in the final result.
So, in my table #dedupe I have 3 records for John Doe...
Application 1 Key 1 Application 2 Key2
Accounting AX Marketing MY
Accounting AX Operations OZ
Marketing MY Operations OZ
Its not always this clean. It can get more complicated with more apps. Relationships can be missing etc.
So currently, my algorithm is coded as shown in the second code block. Is there a more elegant way to code the 2nd update statement? The general idea in this algorithm is that the inner while exhausts all possible transitive permutations before the outer while visits the next candidate (unmarked) record in the table. I'm not sure I like 2 left joins in that update statement instead of maybe a more elegant exists , intersect etc based statement.
---------------------------------------------------------------------------------------------------------------------- --reduce the key mappings from n translation sources into permutations of key pairs to make this easier to manage. --de duplicate the pairs once loaded into the dedupe temp table ---------------------------------------------------------------------------------------------------------------------- Create table #Dedupe ( DedupeId int identity(1,1), TranslationTable varchar(250), Application1 varchar(250), Application2 varchar(250), Key1 varchar(250),--<------------------------------------guids will cast automatically Key2 varchar(250),--<------------------------------------guids will cast automatically [Action] char(1), CompositeKey varchar(10))
------------------------------------------------------------------------------------------------------------------------
--we have to rejoin related records at this point. Going from pairings like we have back to full relationships in
--a transitive manner requires special treatment because of the possibility that nulls exist. Every record is visited
--in this next block and all possible relationships are exhausted before we move on to the next record. A final
--error check has to be made after this block is run because of the transitive nature of this business problem.
------------------------------------------------------------------------------------------------------------------------
set @dedupeSearch1 = 1--<------------------------------------use this to visit each dedupe record
while @dedupeSearch1 <= @countDedupes
Begin
select @Application1 = Application1,@Application2 = Application2,@Key1 = Key1,@Key2 = Key2,@Action = [Action],@CompositeKey = CompositeKey
from #Dedupe
where DedupeId = @dedupeSearch1
--------------------------------------------------------------------------
--process only non excluded, nondiscrepant and non assigned dedupe records
--------------------------------------------------------------------------
If @Action = '' and @Compositekey = ''
Begin
------------------------------------------------------------------------------------------------------------
--a new composite key for new relationships is necessary (this is only internal, external will be a seq guid)
------------------------------------------------------------------------------------------------------------
set @composite = @composite + 1
update #dedupe set CompositeKey = @composite where DedupeId = @dedupeSearch1
set @updateCount = 1
while @updateCount > 0
begin
update a
set a.CompositeKey = @composite
from #dedupe a
------------------------------------------------------------------------------------------------
--looking for more elegant way, maybe dump b and c into 1 temp table, maybe exists and intersect
------------------------------------------------------------------------------------------------
left join
(
select Application1, Key1 from #dedupe where CompositeKey = @composite and (coalesce(Key1,'')<>'')
union all
select Application2, Key2 from #dedupe where CompositeKey = @composite and (coalesce(Key2,'')<>'')
) b
on a.Application1 = b.Application1 and a.Key1=b.key1
left join
(
select Application1, Key1 from #dedupe where CompositeKey = @composite and (coalesce(Key1,'')<>'')
union all
select Application2, Key2 from #dedupe where CompositeKey = @composite and (coalesce(Key2,'')<>'')
) c
on a.Application2 = c.Application1 and a.Key2=c.key1
where a.[Action] = '' and
a.CompositeKey = '' and
((coalesce(b.key1,'')<>'') or (coalesce(c.key1,'')<>''))
set @updateCount = @@RowCount
end
End
set @dedupeSearch1 = @dedupeSearch1 + 1
EndHi db042190,
Would you like following one ?
IN following script , I will replace LEFT JOIN into exists and you can try it to see if satisfies your requirement. By the way, why would you like to use LEFT JOIN? When you use LEFT JOIN , the left table will be shown completely and in your script it seems to make no sense.
set @dedupeSearch1 = 1--<------------------------------------use this to visit each dedupe record while @dedupeSearch1 <= @countDedupes Begin select @Application1 = Application1,@Application2 = Application2,@Key1 = Key1,@Key2 = Key2,@Action = [Action],@CompositeKey = CompositeKey from #Dedupe where DedupeId = @dedupeSearch1 -------------------------------------------------------------------------- --process only non excluded, nondiscrepant and non assigned dedupe records -------------------------------------------------------------------------- If @Action = '' and @Compositekey = '' Begin ------------------------------------------------------------------------------------------------------------ --a new composite key for new relationships is necessary (this is only internal, external will be a seq guid) ------------------------------------------------------------------------------------------------------------ set @composite = @composite + 1 update #dedupe set CompositeKey = @composite where DedupeId = @dedupeSearch1 set @updateCount = 1 while @updateCount > 0 begin ;with cte as ( select Application1, Key1 from #dedupe where CompositeKey = @composite and (coalesce(Key1,'')<>'') union all select Application2, Key2 from #dedupe where CompositeKey = @composite and (coalesce(Key2,'')<>'') ) update a set a.CompositeKey = @composite from #dedupe a ------------------------------------------------------------------------------------------------ --looking for more elegant way, maybe dump b and c into 1 temp table, maybe exists and intersect ------------------------------------------------------------------------------------------------ where (exists (select * from cte b where a.Application1 = b.Application1 and a.Key1=b.key1 and (coalesce(b.key1,'')<>'') or exists (select * from cte c where a.Application2 = c.Application1 and a.Key2=c.key1 and (coalesce(c.key1,'')<>'')) and a.[Action] = '' and a.CompositeKey = '' set @updateCount = @@RowCount end End set @dedupeSearch1 = @dedupeSearch1 + 1 End
Hope it can help you.
Best Regards,
Rachel
这篇关于更加优雅的基于复合标准更新表格的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
