如何从表中选择重复项

Considering this example of a very large table named 'Customer':

考虑这个名为“Customer”的非常大的表的示例：

CustomerID   | LastName | FirstName | Address | City |
-------------|----------|-----------|---------|------|
1520         | Guere    |Francisco  |XPTO     |London|**
2214         | Santos   |António    |TUDO     |Porto |
3998         | Freixe   |Luís       |IUPO     |Quebec|
1520         | Guere    |Francisco  |XPTO     |Rome  |**
5691         | Anton    |Ana        |TIDI     |Lisbon|
1520         | Guere    |Francisco  |XPTO     |Rome  |**

As you can see from this small example, for the same customer (CustomerID = 1520) there is different values in the City column.

从这个小例子可以看出，对于同一个客户（CustomerID = 1520），City列中有不同的值。

For example, the desired output in this case would be:

例如，在这种情况下，所需的输出将是：

 CustomerID | City |
 -----------|------|
 1520       |London|
 1520       |Rome  |

I tried to develop a query that return the CustomerID that have different values in the City column.

我尝试开发一个返回City列中具有不同值的CustomerID的查询。

SELECT CustomerID, City 
FROM (SELECT CustomerID, COUNT(DISTINCT City) FROM Customer GROUP BY CustomerID) 
WHERE City > 1

Can someone help me on what's wrong with the query i developed?

有人可以帮我解决我开发的查询有什么问题吗？

3 个解决方案

#1

You can use the following:

您可以使用以下内容：

SELECT CustomerID, City
FROM Customer
WHERE CustomerID IN (
  SELECT CustomerID
  FROM Customer
  GROUP BY CustomerID
  HAVING COUNT(DISTINCT City) > 1
)
GROUP BY CustomerID, City;

Result:

结果：

| CustomerID | City   |
|------------|--------|
| 1520       | London |
| 1520       | Rome   |

#2

You can use exists if you want the cities:

如果你想要城市，你可以使用存在：

select c.*
from customers c
where exists (select 1
              from customers c2
              where c2.customerid = c.customerid and c2.city <> c.city
             );

Or you can use group by, if you just want the customers:

或者，如果您只是想要客户，您可以使用group by：

select customerid
from customers
group by customerid
having min(city) <> max(city);

#3

You could use HAVING to filter:

您可以使用HAVING过滤：

SELECT *
FROM Customer
WHERE CustomerID IN(SELECT CustomerID
                    FROM Customer 
                    GROUP BY CustomerID
                    HAVING COUNT(DISTINCT City) > 1);

#1