Динамически создавать столбцы из строк в MySQL [дубликат]

На этот вопрос уже есть ответ здесь:

У меня есть следующие таблицы: 

"crawlresults"
id  |   url                 | fk_crawljobs_id
---------------------------------------------
1   |   shop*com/notebooks  |   1
2   |   shop*com/fridges    |   1
3   |   website*com/lists   |   2


"extractions"
id  | fk_extractors_id  | data          |   fk_crawlresults_id
---------------------------------------------------------------
1   |   1               | 123.45        |   1
2   |   2               | notebook      |   1
3   |   3               | ibm.jpg       |   1
4   |   1               | 44.5          |   2
5   |   2               | fridge        |   2
6   |   3               | picture.jpg   |   3
7   |   4               | hello         |   3
8   |   4               | world         |   3
9   |   5               | hi            |   3
10  |   5               | my            |   3
11  |   5               | friend        |   3


"extractors"
id  |   extractorname
----------------------
1   |   price
2   |   article
3   |   imageurl
4   |   list_1
5   |   list_2

Мне нужно построить оператор select, чтобы получить столбцы для каждого экстрактора в таблице экстракторов, которая используется в таблице экстракций.

Пример: 

url                 | price     | article   | imageurl
--------------------------------------------------------
shop*com/notebooks  | 123.45    | notebook  | ibm.jpg
shop*com/fridges    | 44.5      | fridge    | NULL

Я не знаю, сколько экстракторных имен существует, когда я выполняю инструкцию select, поэтому она должна быть динамически построена.

Редактировать: Я забыл упомянуть, что, возможно, у меня есть несколько "списков" в моих извлечениях. В этом случае мне нужен следующий результирующий набор.

Пример 2: 

url                 | list_1    | imageurl      | list_2
--------------------------------------------------------
website*com/lists   | hello     | picture.jpg   | NULL
website*com/lists   | world     | picture.jpg   | NULL
website*com/lists   | NULL      | picture.jpg   | hello
website*com/lists   | NULL      | picture.jpg   | my
website*com/lists   | NULL      | picture.jpg   | friend

Спасибо!

1 2
sql mysql pivot

1 ответ:

Вы ищете динамические сводные таблицы.

Код: 

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pa.extractorname = ''',
      extractorname,
      ''', p.data, NULL)) AS ',
      extractorname
    )
  ) INTO @sql
FROM extractors;

SET @sql = CONCAT('SELECT c.url, ', 
  @sql, 
  ' FROM crawlresults c', 
  ' INNER JOIN extractions p on (c.id = p.fk_crawlresults_id)', 
  ' INNER JOIN extractors pa on (p.fk_extractors_id = pa.id)'
  ' WHERE c.fk_crawljobs_id = 1',
  ' GROUP BY c.id');

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

Рабочая скрипка

В основном ваш исходный запрос генерировал фиктивную переменную @sql, которая на самом деле не извлекала data для каждого extractorname. Вам также не нужны все эти соединения для создания @sql. Вам нужно только каждое из имен свойств (из таблицы extractor) и ссылка на столбец, содержащий ожидаемые значения (data).

Когда сомневаешься в структура, напишите простой сводный запрос для фиксированного набора свойств. Таким образом, становится легко определить шаблон для написания динамического запроса.

SELECT c.url, 
  MAX(IF(pa.extractorname = 'price', p.data, NULL)) AS price,
  MAX(IF(pa.extractorname = 'article', p.data, NULL)) AS article,
  MAX(IF(pa.extractorname = 'imageurl', p.data, NULL)) AS imageurl 
FROM crawlresults c 
  LEFT JOIN extractions p on (c.id = p.fk_crawlresults_id) 
  LEFT JOIN extractors pa on (p.fk_extractors_id = pa.id) 
WHERE c.fk_crawljobs_id = 1
GROUP BY c.id

Что касается остальной части вашего запроса, это нормально, просто имейте в виду, что LEFT JOINS может быть полезно, если нет extractions для некоторых crawlresults. Кроме того, если ваша таблица может содержать более одного crawlresult в url / fk_crawljobs_id, группировка по url - плохая идея (MAX потенциально может смешивать результаты из нескольких extractions).

2