SQL服务器 - INNER JOIN具有鲜明
问题描述:
我有一个很难做到以下几点:SQL服务器 - INNER JOIN具有鲜明
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)
我想要做ValTbl但仅用于不同值的加入。
答
试试这个:
select distinct a.FirstName, a.LastName, v.District
from AddTbl a
inner join ValTbl v
on a.LastName = v.LastName
order by a.FirstName;
还是这个(它是相同的,但语法不同):
select distinct a.FirstName, a.LastName, v.District
from AddTbl a, ValTbl v
where a.LastName = v.LastName
order by a.FirstName;
答
“select”后添加“distinct”。
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
答
这是不一样的做一个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)
试试。
答
您可以使用CTE来获取第二个表的不同值,然后将其与第一个表加入。 您还需要根据姓氏列获取不同的值。您可以使用由姓氏分区的Row_Number()来执行此操作,并按FirstName进行排序。
下面的代码
;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
答
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
'NATURAL JOIN'在MSSQL 2008 R1中是'不正确的语法' – itsho 2014-12-09 07:10:18