Решение

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

 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(all_tags, ',', num), ',', -1) AS one_tag, COUNT(*) AS cnt FROM ( SELECT GROUP_CONCAT(tags separator ',') AS all_tags, LENGTH(GROUP_CONCAT(tags SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(tags SEPARATOR ','), ',', '')) + 1 AS count_tags FROM test ) t JOIN numbers n ON n.num <= t.count_tags GROUP BY one_tag ORDER BY cnt DESC; 

Возвращает:

 +---------------------+-----+ | one_tag | cnt | +---------------------+-----+ | chicken | 5 | | pork | 4 | | spaghetti | 3 | | fried-rice | 2 | | manchurain | 2 | | pho | 1 | | chicken-calzone | 1 | | fettuccine | 1 | | chorizo | 1 | | meat-balls | 1 | | miso-soup | 1 | | chanko-nabe | 1 | | chicken-manchurian | 1 | | pork-manchurian | 1 | | sweet-and-sour-pork | 1 | | peking-duck | 1 | | duck | 1 | +---------------------+-----+ 17 rows in set (0.01 sec) 

См. Sqlfiddle

Explaination

сценарий

1. Мы объединяем все теги с помощью запятой, чтобы создать только один список тегов вместо одной строки
2. Мы подсчитываем, сколько тегов у нас в нашем списке
3. Мы находим, как мы можем получить одно значение в этом списке
4. Мы находим, как мы можем получить все значения в виде отдельных строк
5. Мы подсчитываем теги, сгруппированные по их значению

контекст

Давайте построим вашу схему:

 CREATE TABLE test ( id INT PRIMARY KEY, tags VARCHAR(255) ); INSERT INTO test VALUES ("1", "pho,pork"), ("2", "fried-rice,chicken"), ("3", "fried-rice,pork"), ("4", "chicken-calzone,chicken"), ("5", "fettuccine,chicken"), ("6", "spaghetti,chicken"), ("7", "spaghetti,chorizo"), ("8", "spaghetti,meat-balls"), ("9", "miso-soup"), ("10", "chanko-nabe"), ("11", "chicken-manchurian,chicken,manchurain"), ("12", "pork-manchurian,pork,manchurain"), ("13", "sweet-and-sour-pork,pork"), ("14", "peking-duck,duck"); 

Объединить весь список тегов

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

 SELECT GROUP_CONCAT(tags SEPARATOR ',') FROM test; 

Возвращает все теги, разделенные запятой:

фо, свинина, жареный-рис, курица, жареный рис, свинина, курица-кальцоне, курица, феттучини, курица, спагетти, курица, спагетти, чоризо, спагетти, фрикадельки, мисо-суп, тянко-набэ, куриное маньчжурская, курица, manchurain, свинина-маньчжурская, свинина, manchurain, кисло-кисло-свинина, свинина, пекинские утки, утка

Подсчитать все теги

Чтобы подсчитать все теги, мы получаем длину полного списка тегов, и мы удаляем длину полного списка тегов после замены , ничем. Добавим 1, так как разделитель находится между двумя значениями.

 SELECT LENGTH(GROUP_CONCAT(tags SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(tags SEPARATOR ','), ',', '')) + 1 AS count_tags FROM test; 

Возвращает:

 +------------+ | count_tags | +------------+ | 28 | +------------+ 1 row in set (0.00 sec) 

Получить тег Nth в списке тегов

Мы используем функцию SUBSTRING_INDEX для получения

 -- returns the string until the 2nd delimiter\'s occurrence from left to right: a,b SELECT SUBSTRING_INDEX('a,b,c', ',', 2); -- return the string until the 1st delimiter, from right to left: c SELECT SUBSTRING_INDEX('a,b,c', ',', -1); -- we need both to get: b (with 2 being the tag number) SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c', ',', 2), ',', -1); 

С такой логикой, чтобы получить 3-й тег в нашем списке, мы используем:

 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(tags SEPARATOR ','), ',', 3), ',', -1) FROM test; 

Возвращает:

 +-------------------------------------------------------------------------------------+ | SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(tags SEPARATOR ','), ',', 3), ',', -1) | +-------------------------------------------------------------------------------------+ | fried-rice | +-------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 

Получить все значения в виде отдельных строк

Моя идея немного сложная:

1. Я знаю, что мы можем создавать строки, соединяя таблицы
2. Мне нужно получить Nth-тег в списке, используя указанный выше запрос

Таким образом, мы создадим таблицу, содержащую все числа от 1 до максимального количества тегов, которые вы можете иметь в своем списке. Если вы можете иметь 1M-значения, создайте 1M записей от 1 до 1 000 000. Для 100 тегов это будет:

 CREATE TABLE numbers ( num INT PRIMARY KEY ); INSERT INTO numbers VALUES ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 ), ( 10 ), ( 11 ), ( 12 ), ( 13 ), ( 14 ), ( 15 ), ( 16 ), ( 17 ), ( 18 ), ( 19 ), ( 20 ), ( 21 ), ( 22 ), ( 23 ), ( 24 ), ( 25 ), ( 26 ), ( 27 ), ( 28 ), ( 29 ), ( 30 ), ( 31 ), ( 32 ), ( 33 ), ( 34 ), ( 35 ), ( 36 ), ( 37 ), ( 38 ), ( 39 ), ( 40 ), ( 41 ), ( 42 ), ( 43 ), ( 44 ), ( 45 ), ( 46 ), ( 47 ), ( 48 ), ( 49 ), ( 50 ), ( 51 ), ( 52 ), ( 53 ), ( 54 ), ( 55 ), ( 56 ), ( 57 ), ( 58 ), ( 59 ), ( 60 ), ( 61 ), ( 62 ), ( 63 ), ( 64 ), ( 65 ), ( 66 ), ( 67 ), ( 68 ), ( 69 ), ( 70 ), ( 71 ), ( 72 ), ( 73 ), ( 74 ), ( 75 ), ( 76 ), ( 77 ), ( 78 ), ( 79 ), ( 80 ), ( 81 ), ( 82 ), ( 83 ), ( 84 ), ( 85 ), ( 86 ), ( 87 ), ( 88 ), ( 89 ), ( 90 ), ( 91 ), ( 92 ), ( 93 ), ( 94 ), ( 95 ), ( 96 ), ( 97 ), ( 98 ), ( 99 ), ( 100 ); 

Теперь мы получаем num (число, являющееся строкой в number ), используя следующий запрос:

 SELECT n.num, SUBSTRING_INDEX(SUBSTRING_INDEX(all_tags, ',', num), ',', -1) as one_tag FROM ( SELECT GROUP_CONCAT(tags SEPARATOR ',') AS all_tags, LENGTH(GROUP_CONCAT(tags SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(tags SEPARATOR ','), ',', '')) + 1 AS count_tags FROM test ) t JOIN numbers n ON n.num <= t.count_tags 

Возвращает:

 +-----+---------------------+ | num | one_tag | +-----+---------------------+ | 1 | pho | | 2 | pork | | 3 | fried-rice | | 4 | chicken | | 5 | fried-rice | | 6 | pork | | 7 | chicken-calzone | | 8 | chicken | | 9 | fettuccine | | 10 | chicken | | 11 | spaghetti | | 12 | chicken | | 13 | spaghetti | | 14 | chorizo | | 15 | spaghetti | | 16 | meat-balls | | 17 | miso-soup | | 18 | chanko-nabe | | 19 | chicken-manchurian | | 20 | chicken | | 21 | manchurain | | 22 | pork-manchurian | | 23 | pork | | 24 | manchurain | | 25 | sweet-and-sour-pork | | 26 | pork | | 27 | peking-duck | | 28 | duck | +-----+---------------------+ 28 rows in set (0.01 sec) 

Ввод тэгов

Как только у нас теперь есть классические строки, мы можем легко подсчитать вхождения каждого тега.

В верхней части этого ответа вы увидите запрос.

У Алена Тиембо хороший ответ, который объясняет многие механики внизу. Однако для его решения требуется временная таблица (номера) для решения проблемы. В качестве последующего ответа я объединим все его шаги в один запрос (используя имя таблицы для вашей исходной таблицы):

  SELECT t.tags, count(*) AS occurence FROM (SELECT tablename.id, SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.tags, ',', numbers.n), ',', -1) tags FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN tablename ON CHAR_LENGTH(tablename.tags) -CHAR_LENGTH(REPLACE(tablename.tags, ',', ''))>=numbers.n-1 ORDER BY id, n) t GROUP BY t.tags ORDER BY occurence DESC, t.tags ASC 

См. SQLFiddle для демонстрационных целей.

Во-первых, вы должны сохранить это, используя таблицу соединений, с одной строкой на столбе и тегом. Однако иногда мы не можем контролировать структуру данных, с которыми мы работаем.

Вы можете делать то, что хотите, если у вас есть список допустимых тегов:

 select vt.tag, count(t.postid) as cnt from validtags vt left join table t on find_in_set(vt.tag, t.tags) > 0 group by vt.tag order by cnt desc; 

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

Итак, ваши таблицы будут иметь следующие столбцы:
1. теги: tag_id, name
2. Сообщения: post_id, category_code
3. int_tags_to_posts: post_id, tag_id

Чтобы получить подсчеты:
select t.name, count(*) from tags t, posts p, int_tags_to_posts i where i.post_id = p.post_id and i.tag_id = t.tag_id group by i.tag_id order by count(*) desc;

Это должно работать:

 SELECT tag, count(0) count FROM ( SELECT tOut.*, REPLACE(SUBSTRING(SUBSTRING_INDEX(tags, ',', ocur_rank), LENGTH(SUBSTRING_INDEX(tags, ',', ocur_rank - 1)) + 1), ',', '') tag FROM ( SELECT @num_type := if(@id_check = tY.id, @num_type + 1, 1) AS ocur_rank, @id_check := tY.id as id_check, tY.* FROM ( SELECT LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) AS num_ocur, id, tags FROM tablename ) tX INNER JOIN (SELECT LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) AS num_ocur, id, tags FROM tablename) tY INNER JOIN (SELECT @num_type := 0, @id_check := 'some_id') tZ ) tOut WHERE ocur_rank <= num_ocur + 1 ) tempTable GROUP BY tag ORDER BY count DESC; 

Замените «tablename» на имя вашей таблицы.

Этот ответ был получен из решения Джесси Пэрринга, опубликованного на этой странице:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#c12113