Pertanyaan Jadual Pangsi MySQL: Menggunakan Lajur Dinamik untuk Analisis Data

Question

Saya menggunakan jadual berikut untuk menyimpan data produk:

mysql>
+--------------+----------------+--------+
|. id |
+--------------+----------------+--------+
|. 1 |. produk pertama |
|. 2 |. produk kedua |
+--------------+----------------+--------+

mysql> PILIH * DARI product_additional;
+-----------------+----------------+
|. id |
+-----------------+----------------+
|. 1 |
|. 1 |
|. 2 |
|. 2 |
|. 2 |
+----------------+----------------+

Gunakan pertanyaan berikut untuk memilih rekod daripada kedua-dua jadual:

mysql>
    p.id
    , p.nama
    , p.penerangan
    ,MAX(IF(pa.fieldname = 'saiz', pa.fieldvalue, NULL)) sebagai `saiz`
    ,MAX(IF(pa.fieldname = 'height', pa.fieldvalue, NULL)) sebagai `height`
    ,MAX(IF(pa.fieldname = 'color', pa.fieldvalue, NULL)) sebagai `color`
DARI produk p
KIRI SERTAI produk_tambahan AS pa PADA p.id = pa.id
KUMPULAN OLEH p.id
+--------------+--------------+--------+-------- ---------+
|. id |
+--------------+--------------+--------+-------- ---------+
|. 1 |. produk pertama |
|. produk2 |
+--------------+--------------+--------+-------- -+--------+
Semuanya berfungsi dengan baik :) 
Memandangkan saya mengisi jadual "tambahan" secara dinamik, alangkah baiknya jika pertanyaan itu juga dinamik. Dengan cara ini saya tidak perlu menukar pertanyaan setiap kali saya memasukkan nama medan dan nilai medan baharu. 

Answer 1

Saya mempunyai cara yang sedikit berbeza untuk mencapai ini daripada jawapan yang diterima. Pendekatan ini mengelakkan penggunaan GROUP_CONCAT yang mempunyai had 1024 aksara secara lalai dan tidak akan berfungsi jika anda mempunyai banyak medan melainkan anda menukar had tersebut.

SET @sql = '';
SELECT
    @sql := CONCAT(@sql,if(@sql='','',', '),temp.output)
FROM
(
    SELECT
      DISTINCT
        CONCAT(
         'MAX(IF(pa.fieldname = ''',
          fieldname,
          ''', pa.fieldvalue, NULL)) AS ',
          fieldname
        ) as output
    FROM
        product_additional
) as temp;

SET @sql = CONCAT('SELECT p.id
                    , p.name
                    , p.description, ', @sql, ' 
                   FROM product p
                   LEFT JOIN product_additional AS pa 
                    ON p.id = pa.id
                   GROUP BY p.id, p.name, p.description');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Klik di sini untuk melihat demo

Answer 2

Dalam MySQL, satu-satunya cara untuk melakukan ini secara dinamik ialah menggunakan pernyataan yang disediakan. Berikut ialah artikel yang bagus tentang mereka:

Jadual pangsi dinamik (tukar baris kepada lajur)

Kod anda akan kelihatan seperti ini:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pa.fieldname = ''',
      fieldname,
      ''', pa.fieldvalue, NULL)) AS ',
      fieldname
    )
  ) INTO @sql
FROM product_additional;

SET @sql = CONCAT('SELECT p.id
                    , p.name
                    , p.description, ', @sql, ' 
                   FROM product p
                   LEFT JOIN product_additional AS pa 
                    ON p.id = pa.id
                   GROUP BY p.id, p.name, p.description');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Lihat Demo

Nota: Had aksara bagi fungsi GROUP_CONCAT ialah 1024 aksara. Lihat kumpulan parameter_concat_max_len