Heim >Datenbank >MySQL-Tutorial >Wie kann man in SQL mehrere Zeilen mit mehreren Spalten dynamisch gruppieren und zusammenführen?
Beim Arbeiten mit tabellarischen Daten müssen Sie häufig Datensätze basierend auf einer oder mehreren Spalten gruppieren und die Ergebnisse in einer einzigen Zeile mit mehreren Spalten zusammenführen. Dies ist besonders nützlich, wenn es um mehrere Werte geht, die jeder Gruppe zugeordnet sind.
Angenommen, Sie haben eine Tabelle mit Testergebnissen, in der jede Zeile einen bestimmten Testtyp und das entsprechende Ergebnis darstellt. Beispielsweise könnte die Tabelle „Ergebnisse“ das folgende Schema haben:
<code class="language-sql">CREATE TABLE Result( WorkOrder varchar(10), TestType varchar(20), Result decimal(10,2) );</code>
und die folgenden Daten:
<code class="language-sql">INSERT INTO Result (WorkOrder, TestType, Result) VALUES ('HP19002316','VitaminA', 10.3), ('HP19002316','VitaminA', 11.3), ('HP19002316','VitaminA', 12.3), ('HP19002316','VitaminB', 13.4), ('HP19002316','VitaminB', 14.4), ('HP19002316','VitaminC', 15.5), ('HP19002316','VitaminD', 17.0)</code>
Um diese Ergebnisse nach „TestType“ zu gruppieren und für jedes Ergebnis eine neue Zeile mit mehreren Spalten zu erstellen, können wir die Operation „Kreuztabelle“ oder „Pivot“ verwenden. Ein Ansatz besteht darin, die Funktion „ROW_NUMBER“ zu verwenden, um jedem Ergebnis innerhalb der Gruppe eine Sequenznummer zuzuweisen und dann die bedingte Aggregation zu verwenden, um die erforderlichen Werte zu extrahieren.
<code class="language-sql">WITH RNs AS( SELECT WorkOrder, TestType, Result, ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN FROM dbo.Result) SELECT WorkOrder, TestType, MAX(CASE RN WHEN 1 THEN Result END) AS Result1, MAX(CASE RN WHEN 2 THEN Result END) AS Result2, MAX(CASE RN WHEN 3 THEN Result END) AS Result3 FROM RNs R GROUP BY WorkOrder, TestType;</code>
Diese Abfrage erzeugt die folgende Ausgabe:
<code>WorkOrder TestType Result1 Result2 Result3 ========================================================== HP19002316 VitaminA 10.3 11.3 12.3 HP19002316 VitaminB 13.4 14.4 NULL HP19002316 VitaminC 15.5 NULL NULL HP19002316 VitaminD 17.0 NULL NULL</code>
Diese Methode ist jedoch auf eine feste Anzahl von Ergebnissen beschränkt. Um eine dynamische Lösung zu erstellen, die eine beliebige Anzahl von Ergebnissen berücksichtigen kann, können wir eine Technik namens „Übergreifend angewendete Zähltabellen“ verwenden.
<code class="language-sql">DECLARE @SQL nvarchar(MAX), @CRLF nchar(2) = NCHAR(13) + NCHAR(10), @MaxTally int; SELECT @MaxTally = MAX(C) FROM (SELECT COUNT(*) AS C FROM dbo.Result GROUP BY WorkOrder, TestType) R; WITH N AS( SELECT N FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)), Tally AS( SELECT TOP (@MaxTally) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I FROM N N1, N N2) SELECT @SQL = N'WITH RNs AS(' + @CRLF + N' SELECT WorkOrder,' + @CRLF + N' TestType,' + @CRLF + N' Result,' + @CRLF + N' ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN --ORDER BY 应为您的 ID/始终递增列' + @CRLF + N' FROM dbo.Result)' + @CRLF + N'SELECT WorkOrder,' + @CRLF + N' TestType,' + @CRLF + --由于不知道 SQL Server 版本,因此使用 FOR XML PATH STUFF((SELECT N',' + @CRLF + CONCAT(N' MAX(CASE RN WHEN ',T.I,N' THEN Result END) AS Result',T.I) FROM Tally T ORDER BY T.I ASC FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,3,N'') + @CRLF + N'FROM RNs R' + @CRLF + N'GROUP BY WorkOrder,' + @CRLF + N' TestType;'; PRINT @SQL; --您的好朋友。 EXEC sys.sp_executesql @SQL;</code>
Diese Abfrage verwendet eine „Zähltabelle“, um eine Folge von Ganzzahlen zu erstellen, die dynamisch eine SQL-Anweisung basierend auf der maximalen Anzahl von Ergebnissen in der Tabelle generiert. Mit dieser Technik können beliebig viele Ergebnisse in einer einzigen Zeile zusammengefasst werden.
Mit diesen Methoden können Sie Tabellendaten effektiv gruppieren und in einem strukturierteren und aussagekräftigeren Format zusammenführen und so die Datenanalyse und -visualisierung vereinfachen.
Das obige ist der detaillierte Inhalt vonWie kann man in SQL mehrere Zeilen mit mehreren Spalten dynamisch gruppieren und zusammenführen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!