使用重复项连接表SQL Server

I have a table

我有一张桌子

col1

1
2

and other table

和其他表

col1    col2    col3

1          1    data value one                                    
1          2    data value one                                    
2          3    data value two

and I want to join both tables to obtain the following result

我想加入两个表来获得以下结果

col1  col2  col3

1       1    data value one
2       3    data value two

The second table have duplicates but I need to join only one (randomly). I've tried with Inner Join, Left Join, Right Join and always returns all rows. Actually I use SQL Server 2008.

第二个表有重复,但我只需要加入一个(随机)。我尝试过使用Inner Join,Left Join,Right Join并始终返回所有行。实际上我使用的是SQL Server 2008。

3 个解决方案

#1

You can use the ROW_NUMBER Function along with ORDER BY NEWID() To get one random row for each value in col1:

您可以使用ROW_NUMBER函数以及ORDER BY NEWID()为col1中的每个值获取一个随机行:

WITH CTE AS
(   SELECT  Col1,
            Col2,
            Col3, 
            [RowNumber] = ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY NEWID())
    FROM    Table2
)
SELECT  *
FROM    Table1
        INNER JOIN CTE
            ON CTE.Col1 = table1.Col1
            AND CTE.RowNumber = 1 -- ONLY GET ONE ROW FOR EACH VALUE

#2

select t1.col1, t2.col2, t2.col3 from table1 t1
cross apply
(select top 1 col2, col3 from table2 where col1 = t1.col1 order by newid()) t2

#3

Use Distinct it will eliminate dups, but you sure both rows will contain same data?

使用Distinct它将消除重复,但你确定两行将包含相同的数据?

#1