Выбрать из таблицы MySQL по одинаковым значениям полей. Как лучше?

Question

[Сергей Сулаков]

Есть таблица учеников:
id|name|addr|langs|times|ages
1|Иван|Киев;Москва|рус;укр|пн;пт|4
2|Петр|Харьков;Москва|рус|пт|4
3|Олег|Рига|рус;лит|пн;вт|5
.......
и т.д.

Из этих данных нужно собрать группу- т.е. выбрать из базы учеников с:
1. Одним городом(пример: Москва - подходят Иван и Петр)
2. Одним языком(пример: рус - все подходят)
3. Одним днем недели(аналогично)
4. Одним возрастом.
Один момент: Если идет "подбор" по Москве, например, то подойдут те, у которых указан город Москва+ могут "иметь" и другие города. Также и по другим полям
Как тут лучше поступить? А то пока в голову приходят только идиотские решения

Answer 1

[Rsa97]

В таком виде толком никак. Для начала нормализуйте таблицу, вынесите города и языки в связанные таблицы, дни недели из текстовой строки преобразуйте в тип данных SET. Тогда поиск будет простым и понятным.

Comments

[Сергей Сулаков]

А после нормализации как осуществить подбор? Я не могу додуматься до алгоритма подбора совпадений

[Rsa97]

Ученики - `pupils` (`id`, `name`, `weekdays`, `age`)
Города - `cities` (`id`, `name`)
Связь 'ученик-город' - `pupils_cities` (`pupil_id`, `city_id`)

SELECT `p`.*
    FROM `pupils` AS `p`
    JOIN `pupils_cities` AS `pc` ON `pc`.`pupil_id` = `p`.`id`
    WHERE `pc`.`city_id` = :city_id

Answer 2

[Марат]

Полностью согласен с Rsa97. Но если нельзя менять структуру БД и нормализовать ее, то ищите по вхождению подстроки в строку:
1)

select * from table where instr ( concat(';',addr,';'),';Москва;') > 0

2)

select * from table where instr ( concat(';',lang,';'),';рус;') > 0

3)

select * from table where instr ( concat(';',times,';'),';пт;') > 0

и т.д. ';' обнесены, чтобы исключить например попадания Нью-Йорк вместо Йорк и т.п.
Но все это жутко(даже бешено) неправильно по структуре и сильно увеличивает шанс хранения ошибочных данных и т.п.
1) Переименуйте поля (addr -> city, times -> dayofweek и т.п.).
2) Создайте таблицы справочники и сделайте внешний ключ на них
3) Используйте правильный тип строки (очень плохо хранить все в текстовом виде, особенно ограниченные множества.)

Comments

[Сергей Сулаков]

Структура будет переделана, однозначно. У меня проблема с подбором. Нужно искать не по заданным адресам, возрасту, а "без них", т.е. запрос "видит" что чаще повторяется - то и выбирает

[Марат]

Под рукой нет mysql, но как то так:

select * from pupils p where  p.id =(
   select c.city_id from cities c, pupils_cities pc
      where 
         c.id=pc.city_id 
      group by 
         pc.city_id 
      order by 
         count(*) 
     desc limit 1,1)

Получите список учеников, живущих в самом распространенном городе.
Поменяйте limit 1,1 на limit 2,1 получите список учеников во втором по популярности городе и т.п.
Принцип понятен?

[Сергей Сулаков]

Joysi75: да, понятен, спасибо!

[Rsa97]

То же самое через JOIN'ы (ну и порядок ORDER BY правильный)

SELECT `p`.* 
    FROM `pupils` AS `p` 
    JOIN `pupils_cities` AS `pc` ON `pc`.`pupil_id` = `p`.`id`
    JOIN (
        SELECT `city_id`
            FROM `pupils_cities`
            GROUP BY `city_id`
            ORDER BY COUNT(*) DESC
            LIMIT 1
    ) AS `cnt` ON `cnt`.`city_id` = `pc`.`city_id`