為特定列選擇僅具有不同值的相同記錄

下面是我的客戶表的範例。有些記錄在 BIRTHDAY DATE 中有多個值（錯誤地左右）。我只想選擇那些 LASTNAME、MIDDLENAME、FIRSTNAME、SSN 值相同但生日不同的記錄：

成員表

LASTNAME   MIDDLENAME   FIRSTNAME    SSN      BIRTHDAY
Jones      M            Carol        1234     17-DEC-45
Jones      M            Carol        1234     17-DEC-45
Jones      M            Carol        4425     20-APR-70
Black      S            Ted          5555     15-MAY-57
Roberts    T            Cole         1412     14-MAY-57
Roberts    T            Cole         1412     20-OCT-57
Roberts    S            Cole         1412     15-MAY-57

我希望結果是：

LASTNAME   MIDDLEANME  FIRSTNAME    SSN      BIRTHDAY
Roberts    T           Cole         1412     14-MAY-57
Roberts    T           Cole         1412     20-OCT-57

請注意，表中很少有具有相同 SSN 或全名的帳戶，它們沒有被選中，因為它們的所有內容都不相同。同樣沒有選擇具有 1234 作為 SSN 的 Jones M. Carol，因為她沒有兩個不同帳戶的不同生日。

這是我到目前為止的 SQL 查詢，但不一定能正常工作。

SELECT x.FIRST_NM, x.MDL_NM, x.LAST_NM, x.SSN, x.BRTH_DT
FROM Member_table x
WHERE EXISTS
(
   SELECT FIRST_NM, MDL_NM, LAST_NM, SSN, COUNT(*)
   from Member_table
   WHERE CURRENT_RECORD_IN = 'Y'
   group by FIRST_NM, MDL_NM, LAST_NM, SSN
   having count(distinct BRTH_DT) > 1
)
ORDER BY FIRST_NM ASC, LAST_NM ASC, MDL_NM ASC, SSN ASC;

對這個查詢有什麼建議嗎？

這是一個使用EXISTS和相關子查詢的範例。我在 SQL Server 上進行了測試，但可能會在其他 RDBMS 上工作。

drop table if exists table1
CREATE TABLE Table1
   (LASTNAME varchar(7), MIDDLENAME varchar(1), FIRSTNAME varchar(5), SSN int, BIRTHDAY varchar(9))
;

INSERT INTO Table1
   (LASTNAME, MIDDLENAME, FIRSTNAME, SSN, BIRTHDAY)
VALUES
   ('Jones', 'M', 'Carol', 1234, '17-DEC-45'),
   ('Jones', 'M', 'Carol', 1234, '17-DEC-45'),
   ('Jones', 'M', 'Carol', 4425, '20-APR-70'),
   ('Black', 'S', 'Ted', 5555, '15-MAY-57'),
   ('Roberts', 'T', 'Cole', 1412, '14-MAY-57'),
   ('Roberts', 'T', 'Cole', 1412, '20-OCT-57'),
   ('Roberts', 'S', 'Cole', 1412, '15-MAY-57')
;


SELECT *
FROM table1 t1
WHERE EXISTS (
       SELECT *
       FROM table1
       WHERE LASTNAME = t1.LASTNAME
           AND MIDDLENAME = t1.MIDDLENAME
           AND FIRSTNAME = t1.FIRSTNAME
           AND SSN = t1.SSN
           AND BIRTHDAY <> t1.BIRTHDAY
       )

---

| LASTNAME | MIDDLENAME | FIRSTNAME | SSN  | BIRTHDAY  |
|----------|------------|-----------|------|-----------|
| Roberts  | T          | Cole      | 1412 | 14-MAY-57 |
| Roberts  | T          | Cole      | 1412 | 20-OCT-57 |

這也適用於 Oracle。

      CREATE TABLE MEMBER 
(
   LASTNAME    VARCHAR2(7),
   MIDDLENAME   CHAR(1),
   FIRSTNAME    VARCHAR2(5),
   SSN          INT,
   BIRTHDAY     VARCHAR2(9)
);

Insert into MEMBER (LASTNAME,MIDDLENAME,FIRSTNAME,SSN,BIRTHDAY) values ('Jones','M','Carol',1234,'17-DEC-45');
Insert into MEMBER (LASTNAME,MIDDLENAME,FIRSTNAME,SSN,BIRTHDAY) values ('Jones','M','Carol',1234,'17-DEC-45');
Insert into MEMBER (LASTNAME,MIDDLENAME,FIRSTNAME,SSN,BIRTHDAY) values ('Jones','M','Carol',4425,'20-APR-70');
Insert into MEMBER (LASTNAME,MIDDLENAME,FIRSTNAME,SSN,BIRTHDAY) values ('Black','S','Ted',5555,'15-MAY-57');
Insert into MEMBER (LASTNAME,MIDDLENAME,FIRSTNAME,SSN,BIRTHDAY) values ('Roberts','T','Cole',1412,'14-MAY-57');
Insert into MEMBER (LASTNAME,MIDDLENAME,FIRSTNAME,SSN,BIRTHDAY) values ('Roberts','T','Cole',1412,'20-OCT-57');
Insert into MEMBER (LASTNAME,MIDDLENAME,FIRSTNAME,SSN,BIRTHDAY) values ('Roberts','S','Cole',1412,'15-MAY-57');
Insert into MEMBER (LASTNAME,MIDDLENAME,FIRSTNAME,SSN,BIRTHDAY) values ('James','N','Rob',7890,'18-JUN-58');
Insert into MEMBER (LASTNAME,MIDDLENAME,FIRSTNAME,SSN,BIRTHDAY) values ('James','N','Rob',7890,'15-JUN-58');
Insert into MEMBER (LASTNAME,MIDDLENAME,FIRSTNAME,SSN,BIRTHDAY) values ('James','N','Rob',7890,'20-MAR-56');
Insert into MEMBER (LASTNAME,MIDDLENAME,FIRSTNAME,SSN,BIRTHDAY) values ('James','N','Rob',7890,'14-APR-55');

SELECT      DISTINCT a.* 
FROM        member a,member b
WHERE       a.lastname=b.lastname
      AND   a.middlename=b.middlename
      AND   a.firstname=b.firstname
      AND   a.ssn=b.ssn
      AND   a.birthday != b.birthday

 ORDER BY  a.lastname,a.middlename,a.firstname,a.ssn,a.birthday;

輸出

James   N   Rob   7890  14-APR-55
James   N   Rob   7890  15-JUN-58
James   N   Rob   7890  18-JUN-58
James   N   Rob   7890  20-MAR-56
Roberts T   Cole  1412  14-MAY-57
Roberts T   Cole  1412  20-OCT-57

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