查找由多個 customer_id 共享且 customer_name 不同的地址

我需要返回與其關聯的多個 customer_ID 的地址列表，排除具有相同 customer_name 的 customer_ID 並返回地址重複的次數

以下是數據的樣子：

CUSTOMER_ID   |   CUSTOMER_NAME   |   ADDRESS           | MEMBER_SINCE
----------------------------------------------------------------------
A1000         |      John Doe     |    123 West Lane    |  2/23/2005

A1001         |      John Doe     |    123 West Lane    |  9/30/2005

A1002         |      Tom White    |    456 East Street  |  5/7/2006

A1003         |      Frank Smith  |    123 West Lane    |  12/12/2006

A1004         |      Lisa Simpson |    456 East Street  |  7/19/2007

A1005         |      Clark Kent   |    700 North Ave.   |  2/23/2008

A1006         |      Darth Vader  |    123 West Lane    |  9/29/2008

A1007         |      John Doe     |    456 East Street  |  5/23/2007

A1008         |      Bart Star    |    456 East Street  |  4/19/2006

我希望結果集看起來像：

CUSTOMER_ID   |   CUSTOMER_NAME   |   ADDRESS           | MEMBER_SINCE| COUNT
--------------------------------------------------------------------------------
A1000         |      John Doe     |    123 West Lane    |  2/23/2005  |  3

A1002         |      Tom White    |    456 East Street  |  5/7/2006   |  4

A1003         |      Frank Smith  |    123 West Lane    |  12/12/2006 |  3

A1004         |      Lisa Simpson |    456 East Street  |  7/19/2007  |  4

A1006         |      Darth Vader  |    123 West Lane    |  9/29/2008  |  3

A1007         |      John Doe     |    456 East Street  |  5/23/2007  |  4

A1008         |      Bart Star    |    456 East Street  |  4/19/2006   |  4

結果顯示 CUSTOMER_ID、CUSTOMER_NAMES、ADDRESSES、MEMBER_SINCE 和共享地址的不同 CUSTOMER_ID 的 COUNT，同時忽略具有相同 ADDRESS 和 CUSTOMER_NAME 的行作為另一行，即使 CUSTOMER_ID 不同。任何不具有多個 CUSTOMER_ID 的 ADDRESSES 也被排除在外。

請注意，結果中不存在 CUSTOMER_ID A1001，因為 CUSTOMER_NAME 和 ADDRESS 相同且必須重複，並且 A1005 不存在，因為沒有其他客戶具有該地址。

這是開始：

SELECT
 CUSTOMER_ID as CUSTOMER_ID,
 "CUSTOMER_NAME" as CUSTOMER_NAME,
 "ADDRESS",
 "MEMBER_SINCE",
 count(CUSTOMER_ID) as COUNTER
FROM CUSTOMER_TABLE
 group by ADDRESS_ID, CUSTOMER_ID, CUSTOMER_NAME
 having 
 count(CUSTOMER_ID)>1 
 order by CUSTOMER_ID; 

但我無法取出重複的 CUSTOMER_NAMES。

這是我在這裡的第一篇文章，對我來說已經很晚了，所以如果我沒有正確發布問題，我提前道歉。

和

ADDRESS上面的查詢按,CUSTOMER_ID和分組CUSTOMER_NAME。由於CUSTOMER_ID是唯一的，因此每個輸出將有 1 行CUSTOMER_ID。

此查詢給出正確的結果：

SELECT
   CUSTOMER_ID = MIN(ct.CUSTOMER_ID)
   , ct.CUSTOMER_NAME
   , ct. ADDRESS
   , MEMBER_SINCE = MIN(ct.MEMBER_SINCE)
   , cnt.c
FROM CUSTOMER_TABLE ct
INNER JOIN (
   SELECT ADDRESS, COUNT(DISTINCT CUSTOMER_NAME) as c
   FROM CUSTOMER_TABLE 
   GROUP BY ADDRESS 
   HAVING COUNT(ADDRESS) > 1
) as cnt ON cnt.ADDRESS = ct.ADDRESS
GROUP BY ct.ADDRESS, ct.CUSTOMER_NAME, cnt.c
ORDER BY MIN(ct.CUSTOMER_ID); 

它應該在這裡按ADDRESS andCUSTOMER_NAME only and pick one of theCUSTOMER_ID andMEMBER_SINCE . I choose theMIN(value)` 分組。

由於您還需要CUSTOMER_ID具有相似地址的不同計數，因此已將其添加到子查詢中。

結果：

CUSTOMER_ID | CUSTOMER_NAME | ADDRESS           | MEMBER_SINCE              | COUNT
A1000       | John Doe      | 123 West Lane     | 2005-02-23 00:00:00.000   | 3
A1002       | Tom White     | 456 East Street   | 2006-05-07 00:00:00.000   | 4
A1003       | Frank Smith   | 123 West Lane     | 2006-12-12 00:00:00.000   | 3
A1004       | Lisa Simpson  | 456 East Street   | 2007-07-19 00:00:00.000   | 4
A1006       | Darth Vader   | 123 West Lane     | 2008-09-29 00:00:00.000   | 3
A1007       | John Doe      | 456 East Street   | 2007-05-23 00:00:00.000   | 4
A1008       | Bart Star     | 456 East Street   | 2006-04-19 00:00:00.000   | 4

請注意，我無權訪問 SAP-Hana。然而，它似乎是非常標準的 SQL，並且查詢應該適用於大多數 RDBMS。

引用自：https://dba.stackexchange.com/questions/143969