Jadual Kandungan
Pengumpulan dinamik dan penggabungan berbilang baris dan lajur dalam SQL
Rumah pangkalan data tutorial mysql Bagaimana untuk Menghimpun dan Menggabungkan Berbilang Baris secara Dinamik dengan Berbilang Lajur dalam SQL?

Bagaimana untuk Menghimpun dan Menggabungkan Berbilang Baris secara Dinamik dengan Berbilang Lajur dalam SQL?

Jan 22, 2025 am 01:02 AM

How to Dynamically Group and Merge Multiple Rows with Multiple Columns in SQL?

Pengumpulan dinamik dan penggabungan berbilang baris dan lajur dalam SQL

Apabila bekerja dengan data jadual, anda selalunya perlu mengumpulkan rekod berdasarkan satu atau lebih lajur dan menggabungkan hasil menjadi satu baris dengan berbilang lajur. Ini amat berguna apabila berurusan dengan pelbagai nilai yang dikaitkan dengan setiap kumpulan.

Andaikan anda mempunyai jadual yang mengandungi keputusan ujian, di mana setiap baris mewakili jenis ujian tertentu dan hasil yang sepadan. Sebagai contoh, jadual "Keputusan" mungkin mempunyai skema berikut:

CREATE TABLE Result(
  WorkOrder varchar(10),
  TestType varchar(20),
  Result decimal(10,2)
);
Salin selepas log masuk

dan data berikut:

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)
Salin selepas log masuk

Untuk mengumpulkan hasil ini mengikut "TestType" dan mencipta baris baharu dengan berbilang lajur untuk setiap hasil, kami boleh menggunakan operasi "Crosstab" atau "Pivot". Satu pendekatan ialah menggunakan fungsi "ROW_NUMBER" untuk memberikan nombor jujukan kepada setiap hasil dalam kumpulan dan kemudian menggunakan pengagregatan bersyarat untuk mengekstrak nilai yang diperlukan.

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;
Salin selepas log masuk

Pertanyaan ini akan menghasilkan output berikut:

<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>
Salin selepas log masuk

Walau bagaimanapun, kaedah ini terhad kepada bilangan keputusan tetap. Untuk mencipta penyelesaian dinamik yang boleh menampung sebarang bilangan hasil, kami boleh menggunakan teknik yang dipanggil "jadual pengiraan gunaan silang."

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;
Salin selepas log masuk

Pertanyaan ini menggunakan "jadual kira" untuk mencipta jujukan integer yang menjana pernyataan SQL secara dinamik berdasarkan bilangan maksimum hasil dalam jadual. Teknik ini membolehkan sebarang bilangan hasil digabungkan menjadi satu baris.

Dengan menggunakan kaedah ini, anda boleh mengumpulkan dan menggabungkan data jadual dengan berkesan ke dalam format yang lebih berstruktur dan bermakna, memudahkan analisis dan visualisasi data.

Atas ialah kandungan terperinci Bagaimana untuk Menghimpun dan Menggabungkan Berbilang Baris secara Dinamik dengan Berbilang Lajur dalam SQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!

Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn

Tag artikel panas

Notepad++7.3.1

Notepad++7.3.1

Editor kod yang mudah digunakan dan percuma

SublimeText3 versi Cina

SublimeText3 versi Cina

Versi Cina, sangat mudah digunakan

Hantar Studio 13.0.1

Hantar Studio 13.0.1

Persekitaran pembangunan bersepadu PHP yang berkuasa

Dreamweaver CS6

Dreamweaver CS6

Alat pembangunan web visual

SublimeText3 versi Mac

SublimeText3 versi Mac

Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

Kurangkan penggunaan memori MySQL di Docker Kurangkan penggunaan memori MySQL di Docker Mar 04, 2025 pm 03:52 PM

Kurangkan penggunaan memori MySQL di Docker

Bagaimana anda mengubah jadual di MySQL menggunakan pernyataan Alter Table? Bagaimana anda mengubah jadual di MySQL menggunakan pernyataan Alter Table? Mar 19, 2025 pm 03:51 PM

Bagaimana anda mengubah jadual di MySQL menggunakan pernyataan Alter Table?

Cara menyelesaikan masalah MySQL tidak dapat membuka perpustakaan bersama Cara menyelesaikan masalah MySQL tidak dapat membuka perpustakaan bersama Mar 04, 2025 pm 04:01 PM

Cara menyelesaikan masalah MySQL tidak dapat membuka perpustakaan bersama

Apa itu SQLite? Gambaran Keseluruhan Komprehensif Apa itu SQLite? Gambaran Keseluruhan Komprehensif Mar 04, 2025 pm 03:55 PM

Apa itu SQLite? Gambaran Keseluruhan Komprehensif

Jalankan MySQL di Linux (dengan/tanpa bekas podman dengan phpmyadmin) Jalankan MySQL di Linux (dengan/tanpa bekas podman dengan phpmyadmin) Mar 04, 2025 pm 03:54 PM

Jalankan MySQL di Linux (dengan/tanpa bekas podman dengan phpmyadmin)

Menjalankan Pelbagai Versi MySQL di MacOS: Panduan Langkah demi Langkah Menjalankan Pelbagai Versi MySQL di MacOS: Panduan Langkah demi Langkah Mar 04, 2025 pm 03:49 PM

Menjalankan Pelbagai Versi MySQL di MacOS: Panduan Langkah demi Langkah

Bagaimanakah saya menjamin MySQL terhadap kelemahan biasa (suntikan SQL, serangan kekerasan)? Bagaimanakah saya menjamin MySQL terhadap kelemahan biasa (suntikan SQL, serangan kekerasan)? Mar 18, 2025 pm 12:00 PM

Bagaimanakah saya menjamin MySQL terhadap kelemahan biasa (suntikan SQL, serangan kekerasan)?

Bagaimana saya mengkonfigurasi penyulitan SSL/TLS untuk sambungan MySQL? Bagaimana saya mengkonfigurasi penyulitan SSL/TLS untuk sambungan MySQL? Mar 18, 2025 pm 12:01 PM

Bagaimana saya mengkonfigurasi penyulitan SSL/TLS untuk sambungan MySQL?

See all articles