返回不同的值，其中一列相同但一列不同

I am trying to return results in TSQL where it only displays addresses where there are multiple names. The tricky part has been there are multiple duplicates already in this table... so the Having Count variations that I've tried do not work because they all have a count greater than one. So I have not been able to easily distinguish unique names that have the same address. The solution illustrated below is what I would like to produce... and I have but my solution is a sad last ditched effort within Access where I ended up using a query with three sub queries to get the results:

我试图在TSQL中返回结果,它只显示有多个名称的地址。棘手的部分是这个表中已经存在多个重复...所以我尝试过的Count Count变量不起作用,因为它们都有一个大于一的计数。所以我无法轻易区分具有相同地址的唯一名称。下面说明的解决方案就是我想要制作的...我有但我的解决方案是在Access中最后一次失败的努力,我最终使用了一个包含三个子查询的查询来获得结果:

Address             Name
101 1st Ave         Brian Wood
101 1st Ave         Amy Wood
101 1st Ave         Adam Wood
555 5th St          Sarah Parker
555 5th St          Parker Corp.

Sample Data Looks Like this:

示例数据看起来像这样:

Address             Name
101 1st Ave         Brian Wood
101 1st Ave         Brian Wood
101 1st Ave         Brian Wood
101 1st Ave         Amy Wood
101 1st Ave         Adam Wood
555 5th St          Sarah Parker
555 5th St          Sarah Parker
555 5th St          Sarah Parker
555 5th St          Parker Corp.

I've been trying to get this for hours... I know their is a much simpler way to do this but as it's been a 16 hour day and it's 2:00 am I just can't get my head around it.

我一直试图让这个好几个小时...我知道他们这是一个更简单的方法,但是因为这是一个16小时的一天,而且是凌晨2点,我只是无法理解它。

Here is an example of my best TSQL results... it does the trick but it bumps it into two different columns:

这是我最好的TSQL结果的一个例子......它可以解决这个问题,但它会把它变成两个不同的列:

SELECT DISTINCT t1.Name, t2.Name, t1.Address
FROM tblLeads t1
  JOIN tblLeads t2 ON t1.Address = t2.Address 
WHERE t1.Name <> t2.Name
ORDER BY t1.Address

4 个解决方案

#1

You can do a GROUP with COUNT(Distinct Name) > 1 to get Address with more than 1 unique name, and then do a select distinct with a filter on the above grouped Addresses like this.

您可以使用COUNT(Distinct Name)> 1进行GROUP以获取具有多个唯一名称的地址,然后对上述分组地址中的过滤器执行select选择。

SELECT DISTINCT Address,Name
From Table1
WHERE Address IN (
SELECT Address
FROM Table1
GROUP BY Address
HAVING COUNT(distinct Name) > 1
)

#2

You could use multiple CTE's to simplify this task. You first want to clean up your data, so remove all those duplicates, therefore you can use DISTINCT. Then use Count(*)OVER(Partition By Address) to get the count of rows per Address:

您可以使用多个CTE来简化此任务。您首先要清理数据,因此请删除所有这些重复项,因此您可以使用DISTINCT。然后使用Count(*)OVER(Partition By Address)来获取每个地址的行数:

WITH CleanedData AS
(
   SELECT DISTINCT Address, Name
   FROM dbo.tblLeads
),
CTE AS
(
   SELECT Address, Name, 
          cnt = Count(*) OVER (Partition By Address)
   FROM CleanedData
)
SELECT Address, Name
FROM CTE
WHERE cnt > 1

Demo

By the way, this works also if Address has null values: Demo (as opposed to this).

顺便说一句,如果Address具有空值:Demo(与此相反),这也适用。

#3

Use EXISTS to verify same addresses but other name:

使用EXISTS验证相同的地址,但使用其他名称:

SELECT DISTINCT t1.LastName, t1.Street
FROM tblLeads t1
WHERE EXISTS (select 1 from tblLeads t2
              where t1.Street = t2.Street
                and t1.LastName <> t2.LastName)
ORDER BY t1.Street

#4

alternative solution to Tim's one without CTE:

Tim没有CTE的替代解决方案:

select address, name
from (select t.*, count(*) over(partition by address) as cnt
  from (select distinct address, name from tblLeads) t
) where  cnt > 1

#1