Как лучше всего выполнить > 100 условий в одном операторе SELECT в MySQL?

У меня есть запрос, который извлекает некоторые группы в Moodle:

select 
    g.id groupid, 
    g.name groupname, 
    count(distinct gm.userid)
from prefix_groups g
    left join prefix_groups_members gm on gm.groupid = g.id
group by g.id

выход

| groupid | groupname | count(distinct gm.userid) |
|---------|-----------|---------------------------|
| 1       | 20NEW-4A  | 6                         |
| 2       | 18PAR-5F  | 3                         |
| 3       | 20BER-6G  | 2                         |
| 4       | 50NEV-6G  | 6                         |
| 5       | 34HOG-5Q  | 77                        |
| 6       | 10BAT-GG  | 5                         |
| etc.    | etc.      | etc.                      |

Я хочу добавить столбец с именем location, в котором указано местоположение группы (в соответствии со стандартом имени группы на этой платформе, например, BER = Berlin). У меня есть более 100 таких местоположений для фильтрации. Я знаю, что могу добавить их все в статус case и назвать это очень долгим днем ​​(например, ниже), но я хочу сделать это максимально эффективно. В этом случае я не могу создать для этого временную таблицу. Есть идеи?

select 
    g.id groupid, 
    g.name groupname, 
    case when substring(g.name, 3, 3) = 'BER'
        then 'Berlin'
        when substring(g.name, 3, 3) = 'NEW'
        then 'Newcastle'
        -- etc.
    end location,
    count(distinct gm.userid)
from prefix_groups g
    left join prefix_groups_members gm on gm.groupid = g.id
group by g.id

выход:

| groupid | groupname | location  | count(distinct gm.userid) |
|---------|-----------|-----------|---------------------------|
| 1       | 20NEW-4A  | Newcastle | 6                         |
| 2       | 18PAR-5F  | Paris     | 3                         |
| 3       | 20BER-6G  | Berlin    | 2                         |
| 4       | 50NEV-6G  | Neverland | 6                         |
| 5       | 34HOG-5Q  | Hogwarts  | 77                        |
| 6       | 10BAT-GG  | Bath      | 5                         |
| etc.    | etc.      | etc.      | etc.                      |

Каково полное содержание g.name? Мы видим только подстроки

juergen d 21.12.2020 07:36

Сохраните отношение (код-местоположение) в отдельную таблицу и присоедините к своему запросу.

Akina 21.12.2020 07:36

@juergend - g.name - это имя группы в соответствии с первым запросом и его выводом.

Zectzozda 21.12.2020 07:41

@ Акина, не уверен, что ты пропустил эту часть моего поста: In this instance, I cannot create a temporary table to do this.. Не стоит вдаваться в подробности, почему здесь, но я в основном ограничен использованием только операторов select для выполнения этого запроса.

Zectzozda 21.12.2020 07:43

Ответ обновлен.

Akina 21.12.2020 07:50

Если вы хотите, чтобы ваш запрос был более читабельным, вы можете поместить логику своего местоположения в функцию (удобно, если вы используете ее более чем в одном месте), поэтому вы должны написать ее в своем запросе, например SELECT... yourFunc(g .name) location — это, конечно, просто сократит ваш запрос, а не улучшит производительность.

kopz 21.12.2020 07:58

Нормализуйте свою схему, чтобы вы не присоединялись к подстроке

Strawberry 21.12.2020 09:37
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
0
7
73
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий
CREATE TABLE locations ( code CHAR(3), location VARCHAR(255) );

INSERT INTO locations VALUES ('BER', 'Berlin'), ('NEW', 'Newcastle'), ... ;

а потом

select 
    g.id groupid, 
    g.name groupname, 
    locations.location,
    count(distinct gm.userid)
from prefix_groups g
left join prefix_groups_members gm on gm.groupid = g.id
LEFT JOIN locations ON substring(g.name, 3, 3) = locations.code
group by g.id, g.name, locations.location;

Для повышения производительности вы можете добавить сгенерированный столбец в prefix_groups и присоединиться к нему, избегая функции в состоянии соединения.


В этом случае я не могу создать временную таблицу

Если это так, то, чтобы избежать длинного CASE, вы можете использовать выражение вроде:

ELT(FIND_IN SET(SUBSTRING(g.name, 3, 3), 'BER,NEW,...'), 'Berlin', 'Newcastle', ...) AS location

Не уверен, что это будет эффективнее. Но короче будет с гарантией.

PS. Вы можете использовать другой вариант функции CASE:

case substring(g.name, 3, 3) when 'BER' then 'Berlin'
                             when 'NEW' then 'Newcastle'
                             -- etc.
                             end location,

Разве не должно быть substring(g.name, 1, 3)?

FanoFN 21.12.2020 07:53

@tcadidot0 См. пример значений в вопросе - необходимо удалить первые две цифры.

Akina 21.12.2020 07:54

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

FanoFN 21.12.2020 07:58

Извините за задержку с ответом. Отметив это как ответ. Я обнаружил, что ваш последний вариант (с использованием регистра и подстроки) работает наиболее плавно. Спасибо за вашу помощь!!

Zectzozda 04.02.2021 02:27

Другие вопросы по теме