SQl 服务器重复加入不同元素的问题 [英] SQl server duplicate joins issue with different elements
本文介绍了SQl 服务器重复加入不同元素的问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
抱歉,我再次发布了一个要求.
Sorry, I am posting again with one more requirement.
任何人都可以帮忙:我试图加入重复的值,但它不是我想要的.
can anyone please help: I tried to join with duplicate values but it is not coming as I wanted.
DROP TABLE IF EXISTS #TestTable1
DROP TABLE IF EXISTS #TestTable2
CREATE TABLE #TestTable1 ([No] varchar(50),[Value1] float,[Desc] varchar(50))
insert into #TestTable1 ([No],[Value1],[Desc])
Values
(N'123953',300.02,N'Extra Pay')
,(N'123953',427.2,N'Basic Hours')
,(N'123953',106.8,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',105.6,N'Basic Hours')
CREATE TABLE #TestTable2 ([No] varchar(50),[Value2] float,[Desc] varchar(50))
insert into #TestTable2 ([No],[Value2],[Desc])
Values
(N'123953',200.02,N'Extra Pay')
,(N'123953',553.02,N'Basic Hours')
,(N'123953',446.67,N'Basic Hours')
,(N'123953',427.2,N'Basic Hours')
,(N'123953',106.8,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',213.6,N'Basic Hours')
,(N'123953',105.6,N'Basic Hours')
期望的输出:
[No],[Desc],[Value1],[Value2],[MatchResult]
,(N'123953',N'Extra Pay',300.02,200.02, False)
,(N'123953',N'Basic Hours',427.2,427.2, True)
,(N'123953',N'Basic Hours',106.8,106.8, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,213.6, True)
,(N'123953',N'Basic Hours',213.6,NULL,NULL)
,(N'123953',N'Basic Hours',105.6,105.6, True)
推荐答案
--在我看来,您应该能够将 row_numbers 强制添加到重复的行上,从而实现一对一连接,而不会留下任何匹配项权限为空
--it looks to me like you should be able to force row_numbers onto duplicate rows, and therfore achieve a one-to-one join, leaving no match on the right as null
SELECT Q1.No, Q1.[desc], Q1.[value1],q2.[value2] FROM
(SELECT [NO] ,
value1,
[desc],
ROW_NUMBER() over(partition by [NO] , value1, [desc] order by [no]) RN
FROM #TestTable1
) Q1
LEFT JOIN
(SELECT [NO] ,
value2,
[desc],
ROW_NUMBER() over(partition by [NO] , value2, [desc] order by [no]) RN
FROM #TestTable2
) Q2
ON Q1.Value1=Q2.Value2 AND
Q1.[No] = Q2.[NO] AND
Q1.[desc] = Q2.[Desc] AND
Q1.RN = Q2.rn
这篇关于SQl 服务器重复加入不同元素的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文