SQL服务器重复加入问题 [英] SQl server duplicate joins issue
本文介绍了SQL服务器重复加入问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
任何人都可以帮忙:我试图加入重复的值,但它不是我想要的.
can anyone please help: I tried to join with duplicate values but it is not coming as I wanted.
CREATE TABLE #TestTable1 ([No] varchar(50),[Value1] float,[Desc] varchar(50))
insert into #TestTable1 ([No],[Value1],[Desc])
Values
(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',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]
(N'123953',N'Basic Hours',427.2,427.2)
,(N'123953',N'Basic Hours',106.8,106.8)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,213.6)
,(N'123953',N'Basic Hours',213.6,NULL)
,(N'123953',N'Basic Hours',105.6,105.6)
注意:由于其他原因,我无法加入 Value 字段.
Note: I can't join on Value field for other reasons.
我尝试使用 row_number()
但仍然不是我想要的输出.提前致谢.
I tried to use row_number()
but still not coming as my desired output.
Thanks in advance.
推荐答案
这看起来如何?
WITH Table1
AS
(
SELECT T1.No,
T1.[Desc],
T1.Value1,
CAST(T1.Value1 AS VARCHAR) + '~' + CAST(ROW_NUMBER() OVER (PARTITION BY T1.No, T1.Value1 ORDER BY T1.Value1 ASC) AS VARCHAR(2)) AS RN
FROM @TestTable1 AS T1
),
Table2
AS
(
SELECT T2.No,
T2.[Desc],
T2.Value2,
CAST(T2.Value2 AS VARCHAR) + '~' + CAST(ROW_NUMBER() OVER (PARTITION BY T2.No, T2.Value2 ORDER BY T2.Value2 ASC) AS VARCHAR(2)) AS RN
FROM @TestTable2 AS T2
)
SELECT T.No,
T.[Desc],
T.Value1,
T2.Value2
FROM Table1 AS T
LEFT
JOIN Table2 AS T2
ON T.No = T2.No
AND T.RN = T2.RN;
这篇关于SQL服务器重复加入问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文