mysql выбирает значения динамических строк в качестве имен столбцов, другой столбец в качестве значения

У меня есть устаревшая таблица пользовательской информации (которая все еще активно используется), и я не могу изменить структуру -

id    name       value
------------------------------
0     timezone   Europe/London
0     language   en
0     country    45
0     something  x
1     timezone   Europe/Paris
1     language   fr
1     country    46

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

Мне нужен SQL-запрос, совместимый с MySQL, который вернет -

id    timezone       language    country  something
---------------------------------------------------
0     Europe/London  en          45       x
1     Europe/Paris   fr          46

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

Самое близкое, что я мог бы найти, это использовать подготовленные операторы https://stackoverflow.com/a/986088/830171, которые сначала получить все возможные/уникальные значения из столбца имени и построить запрос, который использует CASE WHEN и/или несколько под-SELECT или JOIN для одних и тех же запросов к таблице.

Альтернативы, которые я могу придумать, заключались бы в том, чтобы получить все строки для этого идентификатора пользователя и обработать их в самом приложении в цикле for или попытаться ограничить имена конечным количеством и использовать sub-SELECTs/JOINs. Однако этот второй вариант не идеален, если будет добавлено новое имя, мне придется вернуться к этому запросу.

Пожалуйста, скажите мне, что я пропустил что-то очевидное


mysql sql pivot-table
person gingerCodeNinja    schedule 07.06.2012    source источник
comment
Вам действительно нужно, чтобы это было выражено в sql, или будет достаточно постобработки с выбранным вами языком программирования? Если вам нужно присоединиться к вашим разобранным данным, то представление/процедура может быть уловкой.   -  person bluevector    schedule 07.06.2012
comment
Да, я пока оставлю беспорядок на уровне приложения - я получу все строки для этого идентификатора пользователя и зациклусь на размещении данных в ассоциативном массиве. Я просто хотел проверить, что это был единственный хороший вариант. По-прежнему очень удобно иметь метод SQL для миграций и разовых отчетов.   -  person gingerCodeNinja    schedule 07.06.2012

Ответы (1)


arrow_upward
46
arrow_downward

В отличие от некоторых других СУБД, MySQL не имеет встроенной поддержки операций поворота такого рода по задумке (разработчики считают, что это больше подходит для уровня представления, а не базы данных, уровня вашего приложения).

Если вам абсолютно необходимо выполнять такие манипуляции в MySQL, создание подготовленного оператора - это путь к цели, хотя вместо того, чтобы возиться с CASE, я бы, вероятно, просто использовал MySQL GROUP_CONCAT():

SELECT CONCAT(
  'SELECT `table`.id', GROUP_CONCAT('
     ,    `t_', REPLACE(name, '`', '``'), '`.value
         AS `', REPLACE(name, '`', '``'), '`'
     SEPARATOR ''),
 ' FROM `table` ', GROUP_CONCAT('
     LEFT JOIN `table`   AS `t_', REPLACE(name, '`', '``'), '`
            ON `table`.id = `t_', REPLACE(name, '`', '``'), '`.id
           AND `t_', REPLACE(name, '`', '``'), '`.name = ', QUOTE(name)
     SEPARATOR ''),
 ' GROUP BY `table`.id'
) INTO @qry FROM (SELECT DISTINCT name FROM `table`) t;

PREPARE stmt FROM @qry;
EXECUTE stmt;

См. его на sqlfiddle.

Обратите внимание, что результат GROUP_CONCAT() ограничен переменная group_concat_max_len (по умолчанию 1024 байта: вряд ли быть уместным здесь, если у вас нет очень длинных значений name).

person eggyal    schedule 07.06.2012
comment
вау - спасибо, что приложили все усилия для написания и тестирования решения. Очень признателен. Я всегда хотел знать, как сделать этот тип запроса, и он выглядит настолько сложным, насколько я себе представлял. Пока я оставлю беспорядок на уровне приложения, но этот запрос по-прежнему будет очень полезен для создания отчетов и миграции. Спасибо. - person gingerCodeNinja; 07.06.2012
comment
У меня очень похожий сценарий, и я получаю развернутый результат, используя ваш отличный пример. Но мне нужно, чтобы результат сохранялся в таблице, так как я хочу создать из него диаграмму. Можно ли сохранить свернутый результат в таблицу? Кроме того, возможно ли выполнить вышеуказанное в Php? Моя исходная таблица - это пользователь, дата, производительность, а сводная таблица - это пользователь в виде строк, дата в виде столбцов и производительность в виде значений ячеек. - person Sri; 16.06.2013
comment
Поместил мою примерную таблицу в sqlfiddle (см. sqlfiddle.com/#!2/06be4/1< /а>) - person Sri; 16.06.2013
comment
Очень хорошо сделано, приведенный выше запрос помог мне исправить и расширить запрос, над которым я работал. Спасибо! - person Baber; 06.06.2014