Запрос сводной таблицы MySQL с динамическими столбцами

Для хранения данных продукта я использую следующие таблицы:

mysql> SELECT * FROM product; +---------------+---------------+--------+ | id | name | description | stock | +---------------+---------------+--------+ | 1 | product1 | first product | 5 | | 2 | product2 | second product| 5 | +---------------+---------------+--------+ mysql> SELECT * FROM product_additional; +-----------------+------------+ | id | fieldname | fieldvalue | +-----------------+------------+ | 1 | size | S | | 1 | height | 103 | | 2 | size | L | | 2 | height | 13 | | 2 | color | black | +-----------------+------------+ 

Используя следующий запрос для выбора записей из обеих таблиц

 mysql> SELECT p.id , p.name , p.description ,MAX(IF(pa.fieldname = 'size', pa.fieldvalue, NULL)) as `size` ,MAX(IF(pa.fieldname = 'height', pa.fieldvalue, NULL)) as `height` ,MAX(IF(pa.fieldname = 'color', pa.fieldvalue, NULL)) as `color` FROM product p LEFT JOIN product_additional AS pa ON p.id = pa.id GROUP BY p.id +---------------+---------------+--------+---------+--------+ | id | name | description | size | height | color | +---------------+---------------+--------+---------+--------+ | 1 | product1 | first product | S | 103 | null | | 2 | product2 | second product| L | 13 | black | +---------------+---------------+--------+---------+--------+ 

И все работает правильно 🙂

Поскольку я заполняю «дополнительную» таблицу динамически, было бы неплохо, если бы запрос также был динамическим. Таким образом, я не должен менять запрос каждый раз, когда я добавляю новое имя поля и значение поля.

    Единственный способ в MySQL сделать это динамически – это подготовленные утверждения. Вот хорошая статья о них:

    Динамические сводные таблицы (преобразование строк в столбцы)

    Ваш код будет выглядеть так:

     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'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 

    См. SQL Fiddle with Demo

    ПРИМЕЧАНИЕ. Функция GROUP_CONCAT имеет ограничение в 1024 символа. См. Параметр group_concat_max_len

    У меня есть несколько иной способ сделать это, чем принятый ответ. Таким образом, вы можете избежать использования GROUP_CONCAT, который имеет ограничение в 1024 символа и не будет работать, если у вас много полей.

     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'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 

    Здесь хранится процедура, которая будет генерировать таблицу на основе данных из одной таблицы и столбца и данных из другой таблицы и столбца.

    Используется функция ‘sum (if (col = value, 1,0)) в качестве значения’. Вы можете выбирать из различных функций, таких как MAX (if ()) и т. Д.

     delimiter // create procedure myPivot( in tableA varchar(255), in columnA varchar(255), in tableB varchar(255), in columnB varchar(255) ) begin set @sql = NULL; set @sql = CONCAT('select group_concat(distinct concat( \'SUM(IF(', columnA, ' = \'\'\',', columnA, ',\'\'\', 1, 0)) AS \'\'\',', columnA, ',\'\'\'\') separator \', \') from ', tableA, ' into @sql'); -- select @sql; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- select @sql; SET @sql = CONCAT('SELECT p.', columnB, ', ', @sql, ' FROM ', tableB, ' p GROUP BY p.', columnB,''); -- select @sql; /* */ PREPARE stmt FROM @sql; EXECUTE stmt; /* */ DEALLOCATE PREPARE stmt; end// delimiter ; 
    Interesting Posts

    Отправка электронной почты с приложением через GMailSender?

    Помещение нескольких строк на графе Excel

    Запись сезона AVI на DVD-плеер без DivX

    Как заставить детей StackPanel заполнить максимальное пространство вниз?

    Поставщик службы Entity Framework не найден для поставщика ADO.NET с инвариантным именем «System.Data.SqlClient»

    Мощность, возводимая в квадрат для отрицательных показателей

    Какой процесс делает этот звук?

    Невозможно сделать статическую ссылку на нестатический метод

    Как включить стандартную копию для TextView в Android?

    Загружать локальный HTML-файл в C # WebBrowser

    Запуск Excel 2007 в качестве администратора в Windows 7 приводит к тому, что «была проблема отправки команды в программу»

    Не удалось выполнить dex: несколько файлов dex определяют

    Как упорядочить список переменных с помощью grid.arrange?

    Код в циклах VBA и никогда не заканчивается. Как это исправить?

    Как сделать OCR в PDF-документе?

    Давайте будем гением компьютера.