Блог

Главная - Вопросы по программированию - Удалите дубликат и подсчет из поля, разделенного запятыми, в MySQL

Удалите дубликат и подсчет из поля, разделенного запятыми, в MySQL

#mysql

Вопрос:

 SELECT SUM(LENGTH(room_id) - LENGTH(REPLACE(room_id, ',', ''))   1) as total 
  FROM booking

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

ID room_id
1 2, 4, 5
2 4, 3

Я хочу показать результат подсчета всего = 4

Теперь он насчитывает 5

Комментарии:

1. Но в вашем примере есть 5 различных значений: 1, 2, 3, 4, 5. В примере говорится «сумма», а в вопросе — «количество». Вам нужно их посчитать или получить сумму всех различных значений?

2. Лучший подход-просто отказаться от текущего дизайна таблицы и прекратить хранить значения CSV таким образом. Вместо этого каждое id - room_id сопряжение должно отображаться в отдельной строке.

3. Что вы пробовали до сих пор? Где ты застрял? Почему столбец room_id содержит что-либо еще, кроме числа?

4. Уважаемый @ekochergin, мне нужно значение счетчика, значение моего столбца разделено запятыми, поэтому я применил СУММУ для подсчета значений, разделенных запятыми. Если у вас есть какой-либо лучший процесс для подсчета значений, разделенных запятыми, в MySQL, пожалуйста, поделитесь мной.

5. SELECT *, (CHAR_LENGTH(room_id) - CHAR_LENGTH(REPLACE(room_id, ',', '')) 1) as total FROM booking Попробуй это

Ответ №1:

Как отметил Тим Бигельштейн, лучший способ-изменить дизайн базы данных и никогда не использовать строки, разделенные запятыми, которые могут потребоваться для разбиения.

Но я понимаю, что это не всегда возможно, поэтому вот решение, которое у меня есть.

К сожалению, это применимо только к mysql версии 8 или новее. В 8-й версии они добавили рекурсивные CTE, которые используются здесь

 with recursive booking_splitted(id, room_id, room_parsed, step, steps_needed) as (
  select id,
         substring(room_id, instr(room_id, ',')   1),
         substring_index(room_id, ',', 1),
         1,
         char_length(room_id) - char_length(replace(room_id, ',', ''))
    from booking
  union all
  select id, 
         substring(room_id, instr(room_id, ',')   1),
         ifnull(substring_index(room_id, ',', 1), room_id),
         step   1,
         steps_needed
    from booking_splitted
   where step <= steps_needed
)

select count(distinct room_parsed) rooms
  from booking_splitted
 order by id;

CTE (часть «с рекурсивным …») разбивает строку, разделенную запятыми, на строки. Итак, линия с

room_id
2,4,5

будет разделен на 3 записи:

room_id
2
4
5

И прошлая часть («выберите количество(различных …») подсчитывает отдельные комнаты

UPD. см., например, dbfiddle

Метки: , ,