SELECT mit berechneter Spalte, die von einer Korrelation abhängig ist

Question

Ich mache nicht viel SQL, und die meiste Zeit mache ich CRUD-Operationen. Gelegentlich werde ich etwas ein bisschen komplizierter. Also, diese Frage kann ein Neuling Frage sein, aber ich bin bereit. Ich versuche schon seit Stunden, das herauszufinden, und es hat nichts gebracht.

Stellen Sie sich also die folgende Tabellenstruktur vor:

> | ID | Col1 | Col2 | Col3 | .. | Col8 |

Ich möchte ID und eine berechnete Spalte auswählen. Die berechnete Spalte hat einen Bereich von 0 - 8 und enthält die Anzahl der Übereinstimmungen mit der Abfrage. Außerdem möchte ich die Ergebnismenge so einschränken, dass sie nur Zeilen enthält, die eine bestimmte Anzahl von Übereinstimmungen aufweisen.

Aus diesen Beispieldaten ergibt sich:

> | 1 | 'a' | 'b' | 1 | 2 |  
> | 2 | 'b' | 'c' | 1 | 2 |  
> | 3 | 'b' | 'c' | 4 | 5 |  
> | 4 | 'x' | 'x' | 9 | 9 |  

Ich möchte auf Col1 = 'a' OR Col2 = 'c' OR Col3 = 1 OR Col4 = 5 abfragen, wobei das berechnete Ergebnis > 1 ist und die Ergebnismenge wie folgt aussehen soll:

> | ID | Cal |
> | 1  |  2  |
> | 2  |  2  |
> | 3  |  2  |

Ich verwende T-SQL und SQL Server 2005, falls das wichtig ist, und ich kann das DB-Schema nicht ändern.

Ich würde es auch vorziehen, es als eine in sich geschlossene Abfrage zu behalten und keine gespeicherte Prozedur oder temporäre Tabelle erstellen zu müssen.

Answer 1

Diese Antwort funktioniert auch mit SQL 2005, wobei eine CTE verwendet wird, um die abgeleitete Tabelle ein wenig zu bereinigen.

WITH Matches AS
(
    SELECT ID, CASE WHEN Col1 = 'a' THEN 1 ELSE 0 END + 
                CASE WHEN Col2 = 'c' THEN 1 ELSE 0 END +
                CASE WHEN Col3 = 1  THEN 1 ELSE 0 END +
                CASE WHEN Col4 = 5  THEN 1 ELSE 0 END AS Result
    FROM Table1
    WHERE Col1 = 'a' OR Col2 = 'c' OR Col3 = 1 OR Col4 = 5 
)
SELECT ID, Result
FROM Matches
WHERE Result > 1

Answer 2

Hier ist eine Lösung, die sich die Tatsache zunutze macht, dass ein boolescher Vergleich die ganzen Zahlen 1 oder 0 zurückgibt:

SELECT * FROM (
  SELECT ID, (Col1='a') + (Col2='c') + (Col3=1) + (Col4=5) AS calculated
  FROM MyTable
) q
WHERE calculated > 1; 

Beachten Sie, dass Sie die booleschen Vergleiche in Klammern setzen müssen, da + hat einen höheren Vorrang als = . Außerdem müssen Sie alles in eine Unterabfrage einfügen, da Sie normalerweise keinen Spaltenalias in einer WHERE Klausel der gleichen Abfrage.

Es mag den Anschein haben, als sollten Sie auch eine WHERE Klausel in der Unterabfrage, um die Zeilen einzuschränken, aber höchstwahrscheinlich werden Sie am Ende ohnehin eine vollständige Tabellendurchsuchung durchführen, so dass dies wahrscheinlich kein großer Gewinn ist. Andererseits, wenn Sie erwarten, dass eine solche Einschränkung sehr die Anzahl der Zeilen im Ergebnis der Unterabfrage zu reduzieren, dann würde es sich lohnen.

---

Re Quassnoi's Kommentar, wenn Sie nicht behandeln können boolesche Ausdrücke als Integer-Werte, sollte es einen Weg, um boolesche Bedingungen zu Integer abbilden, auch wenn es ein bisschen langwierig ist. Zum Beispiel:

SELECT * FROM (
  SELECT ID, 
      CASE WHEN Col1='a' THEN 1 ELSE 0 END
    + CASE WHEN Col2='c' THEN 1 ELSE 0 END 
    + CASE WHEN Col3=1   THEN 1 ELSE 0 END
    + CASE WHEN Col4=5   THEN 1 ELSE 0 END AS calculated
  FROM MyTable
) q
WHERE calculated > 1;

Answer 3

Diese Abfrage ist indexfreundlicher:

SELECT  id, SUM(match)
FROM    (
        SELECT  id, 1 AS match
        FROM    mytable
        WHERE   col1 = 'a'
        UNION ALL
        SELECT  id, 1 AS match
        FROM    mytable
        WHERE   col2 = 'c'
        UNION ALL
        SELECT  id, 1 AS match
        FROM    mytable
        WHERE   col3 = 1
        UNION ALL
        SELECT  id, 1 AS match
        FROM    mytable
        WHERE   col4 = 5
        ) q
GROUP BY
        id
HAVING  SUM(match) > 1

Dies ist nur dann effizient, wenn alle die gesuchten Spalten sind erstens indiziert und zweitens haben sie eine hohe Kardinalität (viele unterschiedliche Werte).

Einzelheiten zur Leistung finden Sie in diesem Artikel in meinem Blog:

• Passend zu 3 von 4