TSQL Group By mit einem "OR"?

Question

Diese Abfrage zur Erstellung einer Liste von Kandidaten-Duplikaten ist einfach genug:

SELECT Count(*), Can_FName, Can_HPhone, Can_EMail
FROM Can 
GROUP BY Can_FName, Can_HPhone, Can_EMail
HAVING Count(*) > 1

Aber wenn die eigentliche Regel, die ich überprüfen möchte, FName und (HPhone OR Email) ist - wie kann ich die GROUP BY anpassen, um mit diesem zu arbeiten?

Ich bin ziemlich sicher, ich werde am Ende mit einem UNION SELECT hier (d.h. tun FName, HPhone auf eine und FName, EMail auf die andere und kombinieren die Ergebnisse) - aber ich würde gerne wissen, wenn jemand einen einfacheren Weg, es zu tun kennt.

Ich danke Ihnen im Voraus für jede Hilfe.

Scott in Maine

Answer 1

Bevor ich Ihnen etwas raten kann, muss ich die Antwort auf diese Frage wissen:

name  phone      email

John  555-00-00  john@example.com
John  555-00-01  john@example.com
John  555-00-01  john-other@example.com

Was COUNT(*) was Sie für diese Daten wollen?

更新しました。

Wenn Sie nur wissen wollen, dass ein Datensatz jede Duplikate, verwenden Sie dies:

WITH    q AS (
        SELECT  1 AS id, 'John' AS name, '555-00-00' AS phone, 'john@example.com' AS email
        UNION ALL
        SELECT  2 AS id, 'John', '555-00-01', 'john@example.com'
        UNION ALL
        SELECT  3 AS id, 'John', '555-00-01', 'john-other@example.com'
        UNION ALL
        SELECT  4 AS id, 'James', '555-00-00', 'james@example.com'
        UNION ALL
        SELECT  5 AS id, 'James', '555-00-01', 'james-other@example.com'
        )
SELECT  *
FROM    q qo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    q qi
        WHERE   qi.id <> qo.id
                AND qi.name = qo.name
                AND (qi.phone = qo.phone OR qi.email = qo.email)
        )

Das ist effizienter, sagt aber nichts darüber aus, wo die doppelte Kette begonnen hat.

Diese Abfrage wählt alle Einträge zusammen mit dem speziellen Feld aus, chainid die angibt, wo die doppelte Kette begonnen hat.

WITH    q AS (
        SELECT  1 AS id, 'John' AS name, '555-00-00' AS phone, 'john@example.com' AS email
        UNION ALL
        SELECT  2 AS id, 'John', '555-00-01', 'john@example.com'
        UNION ALL
        SELECT  3 AS id, 'John', '555-00-01', 'john-other@example.com'
        UNION ALL
        SELECT  4 AS id, 'James', '555-00-00', 'james@example.com'
        UNION ALL
        SELECT  5 AS id, 'James', '555-00-01', 'james-other@example.com'
        ),
        dup AS (
        SELECT  id AS chainid, id, name, phone, email, 1 as d
        FROM    q
        UNION ALL
        SELECT  chainid, qo.id, qo.name, qo.phone, qo.email, d + 1
        FROM    dup
        JOIN    q qo
        ON      qo.name = dup.name
                AND (qo.phone = dup.phone OR qo.email = dup.email)
                AND qo.id > dup.id
        ),
        chains AS 
        (
        SELECT  *
        FROM    dup do
        WHERE   chainid NOT IN
                (
                SELECT  id
                FROM    dup di
                WHERE   di.chainid < do.chainid
                )
        )
SELECT  *
FROM    chains
ORDER BY
        chainid

Answer 2

GROUP BY unterstützt kein OR - es ist implizit AND und muss jeden Nicht-Aggregator in der Auswahlliste enthalten.

Answer 3

Ich nehme an, Sie haben auch eine eindeutige ID Integer als Primärschlüssel in dieser Tabelle. Wenn nicht, ist es eine gute Idee, einen solchen zu haben, für diesen und viele andere Zwecke.

Finden Sie diese Duplikate durch einen Self-Join:

select
  c1.ID 
, c1.Can_FName
, c1.Can_HPhone
, c1.Can_Email
, c2.ID 
, c2.Can_FName
, c2.Can_HPhone
, c2.Can_Email
from
(
  select 
      min(ID), 
      Can_FName, 
      Can_HPhone, 
      Can_Email 
  from Can 
  group by 
      Can_FName, 
      Can_HPhone, 
      Can_Email
) c1
inner join Can c2 on c1.ID < c2.ID 
where
    c1.Can_FName = c2.Can_FName 
and (c1.Can_HPhone = c2.Can_HPhone OR c1.Can_Email = c2.Can_Email)
order by
  c1.ID

Die Abfrage liefert Ihnen N-1 Zeilen für jede N doppelte Kombinationen - wenn Sie nur eine Zählung zusammen mit jeder eindeutigen Kombination wünschen, zählen Sie die nach der "linken" Seite gruppierten Zeilen:

select count(1) + 1,
, c1.Can_FName
, c1.Can_HPhone
, c1.Can_Email
from 
(
  select 
      min(ID), 
      Can_FName, 
      Can_HPhone, 
      Can_Email 
  from Can 
  group by 
      Can_FName, 
      Can_HPhone, 
      Can_Email
) c1
inner join Can c2 on c1.ID < c2.ID 
where
    c1.Can_FName = c2.Can_FName 
and (c1.Can_HPhone = c2.Can_HPhone OR c1.Can_Email = c2.Can_Email)
group by 
  c1.Can_FName
, c1.Can_HPhone
, c1.Can_Email

Zugegeben, das ist komplizierter als eine Vereinigung - aber ich denke, es ist ein gutes Beispiel dafür, wie man über Duplikate denkt.

Answer 4

Projizieren Sie die gewünschte Transformation zunächst aus einer abgeleiteten Tabelle und führen Sie dann die Aggregation durch:

SELECT COUNT(*) 
    , CAN_FName
    , Can_HPhoneOrEMail
    FROM (
        SELECT Can_FName 
            , ISNULL(Can_HPhone,'') +  ISNULL(Can_EMail,'')  AS Can_HPhoneOrEMail
        FROM Can) AS Can_Transformed
    GROUP BY Can_FName, Can_HPhoneOrEMail
    HAVING Count(*) > 1

Passen Sie Ihre 'OR'-Verknüpfung in der Projektliste der abgeleiteten Tabelle nach Bedarf an.

Answer 5

Ich weiß, dass diese Antwort wegen der Verwendung der Aushilfstabelle kritisiert werden wird, aber sie wird trotzdem funktionieren:

-- create temp table to give the table a unique key
create table #tmp(
ID int identity,
can_Fname varchar(200) null, -- real type and len here
can_HPhone varchar(200) null, -- real type and len here
can_Email varchar(200) null, -- real type and len here
)

-- just copy the rows where a duplicate fname exits 
-- (better performance specially for a big table)
insert into #tmp 
select can_fname,can_hphone,can_email
from Can 
where can_fname exists in (select can_fname from Can 
group by can_fname having count(*)>1)

-- select the rows that have the same fname and 
-- at least the same phone or email
select can_Fname, can_Hphone, can_Email  
from #tmp a where exists
(select * from #tmp b where
a.ID<>b.ID and A.can_fname = b.can_fname
and (isnull(a.can_HPhone,'')=isnull(b.can_HPhone,'')
or  (isnull(a.can_email,'')=isnull(b.can_email,'') )