MySQL запрос с групповым контактом

Question

Допустим, у меня есть таблица "test" со следующим дизайном:

SELECT type, name, `key` FROM test

тип | имя | ключ ------------------------ 0 | Мария | 123
  1  | Габриэль | 455
  0  | Рианна | 69
  1  | Крис | 7
  1  | мартин | 112 

Следующий запрос позволяет мне получить все данные в одну строку:

SELECT GROUP_CONCAT(type ORDER BY type) types, GROUP_CONCAT(name ORDER BY type) names, GROUP_CONCAT(`key` ORDER BY type) `keys` FROM test

  типы | имена | ключи
-------------------------------------------------- ----------------
0,0,1,1,1 | Рианна, Мария, Мартин, Крис, Габриэль | 69,123,112,7,455

Но это не совсем то, что мне нужно. Было бы идеально, если бы мне удалось создать запрос, который возвращает следующий результат:

types_0 | names_0 | ключи_0  | types_1 |         names_1         |    keys_1
------------------------------------------------------------------------------------
  0, 0  | Мария, Рианна |  123, 69 |   1, 1  | Габриэль, Крис, Мартин | 455, 7, 112

Есть ли способ создать такой запрос? или это вообще не имеет смысла?

Заранее спасибо.

Answer 1

Это возможно, но я бы этого не сделал. Это будет выглядеть примерно так:

SELECT * FROM 
(
  SELECT 
    GROUP_CONCAT(type ORDER BY type) types, 
    GROUP_CONCAT(name ORDER BY type) names, 
    GROUP_CONCAT(`key` ORDER BY type) `keys` 
  FROM test
  WHERE type = 0
) AS _type0,
(
  SELECT 
    GROUP_CONCAT(type ORDER BY type) types, 
    GROUP_CONCAT(name ORDER BY type) names, 
    GROUP_CONCAT(`key` ORDER BY type) `keys` 
  FROM test
  WHERE type = 1
) AS _type1;

Нет способа генерировать больше столбцов динамически, если он находит больше типов. Это типично для запросов сводной таблицы - вы должны знать различные значения, прежде чем писать запрос.

Я бы вместо этого сделал это:

SELECT 
  type,
  GROUP_CONCAT(name ORDER BY name) names, 
  GROUP_CONCAT(`key` ORDER BY name) `keys` 
FROM test
GROUP BY type;

И вывод должен выглядеть так:

 type |         names        |  keys
------------------------------------------------------------------
 0    | maria,rihanna        | 123,69
 1    | chris,gabriel,martin | 7,455,112

редактировать: я сделал этот порядок запросов name в каждой группе по предложению от ответа @GarethD.

Answer 2

Вы можете сделать это используя это:

SELECT GROUP_CONCAT(CASE WHEN type = 0 THEN type END ORDER BY Type) AS types_0,
       GROUP_CONCAT(CASE WHEN type = 0 THEN name END ORDER BY Type) AS names_0,
       GROUP_CONCAT(CASE WHEN type = 0 THEN `key` END ORDER BY Type) AS keys_0,
       GROUP_CONCAT(CASE WHEN type = 1 THEN type END ORDER BY Type) AS types_1,
       GROUP_CONCAT(CASE WHEN type = 1 THEN name END ORDER BY Type) AS names_1,
       GROUP_CONCAT(CASE WHEN type = 1 THEN `key` END ORDER BY Type) AS keys_1
FROM   Test;

Пример по SQL Fiddle

Или, если вы не знаете количество типов, вы можете генерировать SQL динамически и использовать подготовленный оператор:

SET @SQL = '';

SELECT CONCAT('SELECT ',
                GROUP_CONCAT(DISTINCT
                    CONCAT('GROUP_CONCAT(CASE WHEN type = ', type, ' THEN type END ORDER BY Type) AS types_', type, ','
                            'GROUP_CONCAT(CASE WHEN type = ', type, ' THEN name END ORDER BY Type) AS names_', type, ','
                            'GROUP_CONCAT(CASE WHEN type = ', type, ' THEN `key` END ORDER BY Type) AS keys_', type
                    )), ' FROM Test;')
INTO    @SQL
FROM    Test;

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Пример по SQL-скрипке

ОДНАКО, если вы можете что-то сделать, это не значит, что вы должны это делать, и я согласен с Биллом Карвином, что наиболее разумным способом отображения этих данных будет группировка по типу. Кроме того, ваш запрос не является детерминированным, вы должны упорядочить что-то большее, чем просто набрать текст, чтобы обеспечить согласованность порядка имен и ключей.