SQL Server - INNER JOIN WITH DISTINCT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8567288/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server - INNER JOIN WITH DISTINCT
提问by Nate Pet
I am having a hard time doing the following:
我很难做到以下几点：
select a.FirstName, a.LastName, v.District
from AddTbl a order by Firstname
inner join (select distinct LastName from
ValTbl v where a.LastName = v.LastName)
I want to do a join on ValTbl but only for distinct values.
我想对 ValTbl 进行连接，但仅适用于不同的值。
采纳答案by kol
Try this:
尝试这个：
select distinct a.FirstName, a.LastName, v.District
from AddTbl a
inner join ValTbl v
on a.LastName = v.LastName
order by a.FirstName;
Or this (it does the same, but the syntax is different):
或者这个（它的作用相同，但语法不同）：
select distinct a.FirstName, a.LastName, v.District
from AddTbl a, ValTbl v
where a.LastName = v.LastName
order by a.FirstName;
回答by Denis M. Kitchen
Nate, I think you actually provided a good start for the correct answer right in your question (you just need the correct syntax). I had this exact same problem, and putting DISTINCT in a sub-query was indeed less costly than what other answers here have proposed.
Nate，我认为您实际上为您的问题中的正确答案提供了一个良好的开端（您只需要正确的语法）。我遇到了完全相同的问题，并且将 DISTINCT 放在子查询中确实比此处提出的其他答案成本更低。
select a.FirstName, a.LastName, v.District
from AddTbl a
inner join (select distinct LastName, District
from ValTbl) v
on a.LastName = v.LastName
order by Firstname
回答by AlfredoVR
It's not the same doing a select distinct at the beginning because you are wasting all the calculated rows from the result.
在开始时执行 select distinct 是不一样的，因为您正在浪费结果中的所有计算行。
select a.FirstName, a.LastName, v.District
from AddTbl a order by Firstname
natural join (select distinct LastName from
ValTbl v where a.LastName = v.LastName)
try that.
试试那个。
回答by Sadra Abedinzadeh
You can use CTE to get the distinct values of the second table, and then join that with the first table. You also need to get the distinct values based on LastName column. You do this with a Row_Number() partitioned by the LastName, and sorted by the FirstName.
您可以使用 CTE 获取第二个表的不同值，然后将其与第一个表连接。您还需要根据 LastName 列获取不同的值。您可以使用按姓氏分区并按名字排序的 Row_Number() 执行此操作。
Here's the code
这是代码
;WITH SecondTableWithDistinctLastName AS
(
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY LastName ORDER BY FirstName) AS [Rank]
FROM AddTbl
)
AS tableWithRank
WHERE tableWithRank.[Rank] = 1
)
SELECT a.FirstName, a.LastName, S.District
FROM SecondTableWithDistinctLastName AS S
INNER JOIN AddTbl AS a
ON a.LastName = S.LastName
ORDER BY a.FirstName
回答by Chris Brickhouse
add "distinct" after "select".
在“选择”之后添加“不同”。
select distinct a.FirstName, a.LastName, v.District , v.LastName
from AddTbl a
inner join ValTbl v where a.LastName = v.LastName order by Firstname
回答by Monwabisi Labeanzo
select distinct a.FirstName, a.LastName, v.District from AddTbl a inner join ValTbl v on a.LastName = v.LastName order by a.FirstName;
选择不同的 a.FirstName, a.LastName, v.District from AddTbl a 内连接 ValTbl v on a.LastName = v.LastName order by a.FirstName;
hope this helps
希望这可以帮助
