代码之家 › 专栏 › 技术社区 › Mastro

选择相同的客户名称,但客户地址不同

tsql sql-server

Mastro · 技术社区 · 16 年前

尝试为同一客户选择所有记录,但地址不同。

所以我可以稍后让用户选择Bob Yonkers,然后选择将Bob的所有记录更新到特定地址。所以我想展示所有可用的记录。

数据示例:

CUSTOMER_NAME, CUSTOMER_ADDRESS
Bob Yonkers  , 42 Satellite Cir
Bob Yonkers  , 667 Orbit St
Bob Yonkers  , 42 Satellite Cir
Bob Yonkers  , 667 Orbit St
David Boom   , 5959 Bush Ave
David Boom   , 5959 Bush Ave
David Boom   , 5959 Bush Ave
David Boom   , 5959 Bush Ave
David Boom   , 5959 Bush Ave
Ruby Tuesday , 123 Highway Ln Apt#1
Ruby Tuesday , 123 Highway Ln
David Boom   ,5959 Bush Ave
David Boom   ,5959 Bush Ave
David Boom   ,5959 Bush Ave

所以查询会返回这些结果…

结果实例:

CUSTOMER_NAME, CUSTOMER_ADDRESS
Bob Yonkers  , 42 Satellite Cir
Bob Yonkers  , 667 Orbit St
Ruby Tuesday , 123 Highway Ln Apt#1
Ruby Tuesday , 123 Highway Ln

任何帮助都将不胜感激。

4 回复 | 直到 16 年前

RBarryYoung 16 年前

这是乔尔的精妙之处:

SELECT distinct t1.* 
FROM [table] t1
INNER JOIN [table] t2 ON t1.Name=t2.Name AND t1.Address<>t2.Address

Joel Coehoorn 16 年前

SELECT * 
FROM [table] t1
INNER JOIN [table] t2 ON t1.Name=t2.Name AND t1.Address<>t2.Address

jle 16 年前

试试看……

select * from (select count(customername) as ct, customername, address from table group by customername, address) t1
where t1.ct>1

lenkite 16 年前

这引起了我的兴趣,因为一个朋友问了我类似的问题。下面的查询将有效地解决问题:

mysql> select DISTINCT CUSTOMER_NAME,CUSTOMER_ADDRESS from CUST_ADDR where CUSTOMER_NAME in (select CUSTOMER_NAME from CUST_ADDR GROUP BY CUSTOMER_NAME HAVING COUNT(DISTINCT CUSTOMER_ADDRESS) > 1 );

+---------------+----------------------+
| CUSTOMER_NAME | CUSTOMER_ADDRESS     |
+---------------+----------------------+
| Bob Yonkers   | 42 Satellite Cir     |
| Bob Yonkers   | 667 Orbit St         |
| Ruby Tuesday  | 123 Highway Ln Apt#1 |
| Ruby Tuesday  | 123 Highway Ln       |
+---------------+----------------------+
4 rows in set (0.01 sec)