SQL GROUP_CONCAT разделен на разные столбцы

Я много искал, но не нашел правильного решения моей проблемы.

Что я хочу делать?

У меня есть 2 таблицы в MySQL: - Страна - Валюта (я соединяю их вместе через CountryCurrency --> из-за отношения "многие ко многим")

См. рабочий пример: http://sqlfiddle.com/#!2/317d3/8/0

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

Я нашел функцию group_concat:

SELECT country.Name, country.ISOCode_2, group_concat(currency.name) AS currency
FROM country
INNER JOIN countryCurrency ON country.country_id = countryCurrency.country_id
INNER JOIN currency ON currency.currency_id = countryCurrency.currency_id
GROUP BY country.name

Это имеет следующий результат:

NAME            ISOCODE_2   CURRENCY

Afghanistan AF          Afghani
Åland Islands   AX          Euro
Albania         AL          Lek
Algeria         DZ          Algerian Dinar
American Samoa  AS          US Dollar,Kwanza,East Caribbean Dollar

Но сейчас я хочу разделить валюты на разные столбцы (валюта 1, валюта 2, ...). Я уже пробовал такие функции, как MAKE_SET(), но это не работает.


mysql sql group-concat
person Bram Wijns    schedule 24.07.2013    source источник
comment
SQL не поддерживает динамическое количество столбцов. Вам придется сделать это в приложении.   -  person Vatev    schedule 24.07.2013
comment
Вы можете использовать логику в курсоре, чтобы сделать это. Но курсор сначала должен был бы посмотреть, сколько столбцов данных требуется вашему результирующему набору. Динамически создавать временную таблицу для заполнения и последующего выбора. Динамическое количество столбцов является проблемой этой задачи.   -  person M T Head    schedule 13.07.2017

Ответы (3)


arrow_upward
6
arrow_downward

Вы можете сделать это с помощью substring_index(). Следующий запрос использует ваш как подзапрос, а затем применяет эту логику:

select Name, ISOCode_2,
       substring_index(currencies, ',', 1) as Currency1,
       (case when numc >= 2 then substring_index(substring_index(currencies, ',', 2), ',', -1) end) as Currency2,
       (case when numc >= 3 then substring_index(substring_index(currencies, ',', 3), ',', -1) end)  as Currency3,
       (case when numc >= 4 then substring_index(substring_index(currencies, ',', 4), ',', -1) end)  as Currency4,
       (case when numc >= 5 then substring_index(substring_index(currencies, ',', 5), ',', -1) end)  as Currency5,
       (case when numc >= 6 then substring_index(substring_index(currencies, ',', 6), ',', -1) end)  as Currency6,
       (case when numc >= 7 then substring_index(substring_index(currencies, ',', 7), ',', -1) end)  as Currency7,
       (case when numc >= 8 then substring_index(substring_index(currencies, ',', 8), ',', -1) end)  as Currency8
from (SELECT country.Name, country.ISOCode_2, group_concat(currency.name) AS currencies,
             count(*) as numc
      FROM country
      INNER JOIN countryCurrency ON country.country_id = countryCurrency.country_id
      INNER JOIN currency ON currency.currency_id = countryCurrency.currency_id
      GROUP BY country.name
     ) t

Выражение substring_index(currencies, ',' 2) занимает список в валютах до второй. Для американского Сомоа это будет 'US Dollar,Kwanza'. Следующий вызов с -1 в качестве аргумента принимает последний элемент списка, которым будет 'Kwanza', который является вторым элементом currencies.

Также обратите внимание, что SQL-запросы возвращают четко определенный набор столбцов. Запрос не может иметь переменное количество столбцов (если вы не используете динамический SQL через оператор prepare).

person Gordon Linoff    schedule 24.07.2013
comment
@Любовь. . . Мои извинения. Я не видел ссылку SQLFiddle в вопросе. Приведенный выше код протестирован и работает. - person Gordon Linoff; 24.07.2013
comment
Идеально работает! Спасибо за быстрый и очень правильный ответ! Я приму это как ответ, поскольку срок истек: p - person Bram Wijns; 24.07.2013

arrow_upward
1
arrow_downward

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

SELECT MAX(c) FROM 
((SELECT count(currency.name) AS c
FROM country
INNER JOIN countryCurrency ON country.country_id = countryCurrency.country_id
INNER JOIN currency ON currency.currency_id = countryCurrency.currency_id
GROUP BY country.name) as t)

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

person JGFMK    schedule 12.07.2017

arrow_upward
-2
arrow_downward

Вы можете использовать динамический SQL, но вам придется использовать процедуру

person jaczes    schedule 24.07.2013