Блог

Допустим, у меня есть таблица, подобная этой:

 name        | address
------------ ----------------
JOHN SMITH  | 123 FAKE ST
JANE SMITH  | 123 FAKE ST
DAN JOHNSON | 456 WHATEVER RD
  

Теперь предположим, что я создаю представление, в котором я делаю GROUP BY address , в результате чего получается что-то вроде этого:

 name                   | address         | group_id
----------------------- ----------------- ---------
JOHN SMITH, JANE SMITH | 123 FAKE ST     | 1
DAN JOHNSON            | 456 WHATEVER RD | 2
  

Есть ли способ, используя только SQL, «развернуть» результаты этой группировки, вот так?

 name        | address         | group_id
------------ ----------------- ---------
JOHN SMITH  | 123 FAKE ST     | 1
JANE SMITH  | 123 FAKE ST     | 1
DAN JOHNSON | 456 WHATEVER RD | 2
  

Одним из вариантов является использование SUBSTRING_INDEX() функции.

Проверьте это сообщение в блоге, где определена SPLIT_STRING() функция: mysql-split-string-function

Да, как отметил ypercube, это возможно, и вам понадобится функция SUBSTRING_INDEX . Вам также потребуется повторно создать строки, что сложно, поскольку mysql не поддерживает рекурсивные запросы.

Вы можете найти обходной путь, вот решение, предполагающее максимум 3 записи, просто для иллюстрации:

 SELECT SUBSTRING_INDEX(name, ',', 1), address, group_id
FROM aggregated a1
WHERE
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', 2), ',', -1), address, group_id
FROM aggregated a2
WHERE name LIKE '%,%'
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', 3), ',', -1), address, group_id
FROM aggregated a3
WHERE name LIKE '%,%,%'
  

Концептуальное уродство и недостатки вышеупомянутого подхода являются одной из причин, по которой вам рекомендуется никогда не выполнять это как обычную, разработанную процедуру в системе (производительность также никогда не будет хорошей): агрегирование записей считается частью уровня представления и не должно использоваться для обратной записи чего-либо в базу данных и всегда иметь возможность извлекать записи из неагрегированного источника.

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

РЕДАКТИРОВАТЬ: я использовал термин агрегирование для group_concat (и подобных), который недостаточно специфичен. Было бы лучше сказать — хранение нескольких значений в одном поле (повторяющаяся группа в одном столбце).

Используйте GROUP BY a.name . Почему вы хотите сгруппировать это по адресу?

Вы в любом случае будете использовать WHERE a.address=b.address правильно!