sql在使用join时删除重复记录

I have a following table structure which can't be change.

我有一个不能改变的表格结构。

I'm trying to join these table and want to avoid duplicate records as well.

我正在尝试加入这些表，并希望避免重复记录。

 select p.ProductId,p.ProductName,inv.Details from Products p
inner join Inventory inv on(p.ProductId = inv.ProductId)

here is SqlFiddle.

这是SqlFiddle。

4 个解决方案

#1

From sqlserver 2008+ you can use cross apply.

从sqlserver 2008+您可以使用cross apply。

With cross apply you can make a join inside the subselect as demonstrated here. With top 1 you get maximum 1 row from table Inventory. It will also be possible to add an 'order by' statement to the subselect. However that seems out of scope for your question.

使用cross apply，您可以在子选择中创建一个连接。使用top 1，您可以从表库存中获得最大一行。还可以向子选择添加“order by”语句。然而，这似乎超出了你的问题范围。

select p.ProductId,p.ProductName,x.Details 
from Products p
cross apply
(SELECT top 1 inv.Details FROM Inventory inv WHERE p.ProductId = inv.ProductId) x

#2

You can use row_number function to remove duplicates

可以使用row_number函数删除重复的内容

   WITH New_Inventory AS
    (
        SELECT Productid, Details,
        ROW_NUMBER() OVER (Partition by Productid ORDER BY details) AS RowNumber
        FROM Products
    ) 
    select p.ProductId,p.ProductName,inv.Details from Products p
    inner join New_Inventory inv on(p.ProductId = inv.ProductId)
    where RowNumber = 1

#3

select p.ProductId,p.ProductName 
,(select top 1 inv.Details from  Inventory inv where (p.ProductId = inv.ProductId))Details
from Products p

or

或

select * from 
(
select p.ProductId,p.ProductName,inv.Details
,ROW_NUMBER()over(partition by p.productid order by p.productid)rn from Products p
inner join Inventory inv on(p.ProductId = inv.ProductId)
)t4 
where rn=1

#4

with cte as 
(select p.ProductId,p.ProductName,inv.Details,
RoW_Number() OVER(Partition by  p.ProductId,p.ProductName,inv.Details
                  Order by p.ProductId,p.ProductName,inv.Details) rn
 from Products p
inner join Inventory inv on(p.ProductId = inv.ProductId))

select ProductId,ProductName,Details from cte where rn = 1

SQLFIDDLE

SQLFIDDLE