Wie kann ich n: m Zuordnungen vergleichen?

Ich habe zwei Tische ( entity und kind ) plus ein: m table ( entity_kind ).

 CREATE TABLE entity ( entity_id INT , name NVARCHAR(100) , PRIMARY KEY(entity_id) ) CREATE TABLE kind ( kind_id INT , name NVARCHAR(100) , PRIMARY KEY(kind_id) ) CREATE TABLE entity_kind ( entity_id INT , kind_id INT , PRIMARY KEY(entity_id, kind_id) ) 

Testdaten:

 INSERT INTO entity VALUES (1, 'Entity A') , (2, 'Entity B') , (3, 'Entity C') INSERT INTO kind VALUES (1, 'Kind 1') , (2, 'Kind 2') , (3, 'Kind 3') , (4, 'Kind 4') INSERT INTO entity_kind VALUES (1, 1) , (1, 3) , (2, 1) , (2, 2) , (3, 4) 

Mein Code so weit:

 DECLARE @selected_entities TABLE ( entity_id INT ) DECLARE @same_kinds BIT; INSERT INTO @selected_entities VALUES (1), (2) -- Missing code here SELECT @same_kinds AS "same_kinds" 

Die Tabelle var @selected_entities ist mit Entitäten gefüllt, die verglichen werden sollen.

Die logischen var @same_kinds sollte angeben, ob die ausgewählten Entitäten genau die gleichen Arten zugewiesen haben.

Wie kann ich das erreichen?

Dies ist ein Vergleich zwei Sätze von Sachen Typ Problem. Die Abfrage, die ich zeigen werde, gibt alle Paare zusammen mit einer Flagge. Sie können problemlos den Vergleich einer Unterabfrage mit dem Ändern der ersten beiden entity in die Tabelle der IDs, die Sie vergleichen möchten, einbinden.

Diese Abfrage hat ein paar Teile. Zuerst produziert es alle Paare von Entitäten aus den Entitätstabellen. Dies ist wichtig, denn dies wird sogar Entitäten, die keine "Arten" mit ihnen verbunden sind, abholen. Sie wollen eine Fahne, anstatt nur eine list von denen, die passen.

Dann ist das Herz der Logik, eine Selbstverknüpfung auf der Entity-Art-Tabelle mit dem Spiel auf "Art" zu machen. Dies wird dann von den beiden Einheiten aggregiert. Das Ergebnis ist eine Anzahl der Arten, die zwei Entitäten teilen.

Die endgültige Logik ist, diese Zählung mit der Anzahl der "Arten" auf jeder Entität zu vergleichen. Wenn alle diese Zählungen gleich sind, dann passen die Entitäten zu. Wenn nicht, tun sie es nicht. Dieser Ansatz geht davon aus, dass es keine Duplikate in entity_kinds .

 select e1.entity_id as e1, e2.entity_id as e2, (case when count(ek1.entity_id) = max(ek1.numkinds) and count(ek2.entity_id) = count(ek1.entity_id) and max(ek1.numkinds) = max(ek2.numkinds) then 1 else 0 end) as IsSame from entity e1 join entity e2 on e1.entity_id < e2.entity_id left outer join (select ek.*, count(*) over (partition by entity_id) as numkinds from entity_kind ek ) ek1 on e1.entity_id = ek1.entity_id left outer join (select ek.*, count(*) over (partition by entity_id) as numkinds from entity_kind ek ) ek2 on e2.entity_id = ek2.entity_id and ek2.kind_id = ek1.kind_id group by e1.entity_id, e2.entity_id; 

Die SQL-Geige ist hier .

Sie können dies mit zwei Schecks tun: Erstens, wenn die Art-count auf jede Einheit ist nicht das gleiche, dann können sie nicht übereinstimmen. Zweitens, vorausgesetzt, die Zählung ist die gleiche, müssen Sie nur eine Art, die nicht mit der list einer willkürlichen anderen Entität (Ich nehme nur die erste Entität in der Vergleichsliste) zu finden. In Code:

 DECLARE @firstEntity int = (SELECT TOP 1 entity_id from @selected_entities) IF EXISTS(SELECT TOP 1 se.entity_id FROM @selected_entities se INNER JOIN entity_kind ek ON ek.entity_id = se.entity_id WHERE ek.kind_id NOT IN (SELECT kind_id from entity_kind where entity_id = @firstEntity) OR ((SELECT COUNT(1) FROM entity_kind WHERE entity_id = ek.entity_id) <> (SELECT COUNT(1) FROM entity_kind WHERE entity_id = @firstEntity))) SET @same_kinds = 0 ELSE SET @same_kinds = 1 
 DECLARE @first_entity_id INT; SET @first_entity_id = (SELECT TOP(1) se.entity_id FROM @selected_entities se); DECLARE @dummyvar INT; SELECT DISTINCT @dummyvar = COUNT(ek.kind_id) FROM dbo.entity_kind ek LEFT JOIN ( SELECT ek.kind_id FROM dbo.entity_kind ek WHERE ek.entity_id = @first_entity_id ) k ON ek.kind_id = k.kind_id WHERE ek.entity_id IN (SELECT se.entity_id FROM @selected_entities se) GROUP BY ek.entity_id; SET @same_kinds = CASE WHEN @@ROWCOUNT = 1 THEN 1 ELSE 0 END; SELECT @same_kinds AS [@same_kinds]; 

Hinweis: @selected_entities sollte so deklariert werden:

 DECLARE @selected_entities TABLE ( entity_id INT PRIMARY KEY )