У меня есть две таблицы; members
и telephone
.
Член может иметь несколько телефонных строк. В каждой телефонной строке могут быть установлены флаги, указывающие тип телефона.
Мне нужен набор результатов, который представляет собой одну строку для каждого члена, содержащую все или некоторые из разных телефонных строк.
Это объединение LEFT JOINS
из разных частей одной и той же таблицы.
Таблицы базы данных имеют полные индексы для всех числовых столбцов.
(вероятно, не так важно, но здесь для полноты)
Участники:
CREATE TABLE 'members' (
'mem_id' smallint(6) NOT NULL AUTO_INCREMENT,
...
'reg' varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'No Reg',
PRIMARY KEY ('mem_id')
) ENGINE=MyISAM AUTO_INCREMENT=968 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Телефон:
CREATE TABLE 'telephone' (
'tel_id' int(8) NOT NULL AUTO_INCREMENT,
'mem_id' int(8) NOT NULL,
'tel_name' varchar(64) CHARACTER SET utf8 NOT NULL,
'tel_num' varchar(24) COLLATE utf8mb4_unicode_ci NOT NULL,
'main' char(1) CHARACTER SET utf8 NOT NULL DEFAULT 'N',
'player' char(1) CHARACTER SET utf8 NOT NULL DEFAULT 'N',
'home' char(1) CHARACTER SET utf8 NOT NULL DEFAULT 'N' COMMENT 'Y= Home Phone',
PRIMARY KEY ('tel_id'),
KEY 'memid' ('mem_id'),
KEY 'main' ('main'),
KEY 'player' ('player'),
KEY 'home' ('home'),
KEY 'telnum' ('tel_num')
) ENGINE=MyISAM AUTO_INCREMENT=2237 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Цель запроса состоит в том, чтобы вернуть одну строку для каждого элемента, детализируя (среди других не отображаемых данных) все связанные с ними номера телефонов и быть способными различаться между каждым числом:
WHERE
применяются только к таблице members
.
SELECT members.mem_id, ... ,
thome.tel_num as thome, tmob.tel_num as tmob,
twork.tel_num as twork
FROM members
LEFT JOIN telephone thome FROM telephone ON thome.mem_id = members.mem_id AND thome.home = 'Y'
LEFT JOIN telephone tmob FROM telephone ON tmob.mem_id = members.mem_id AND tmob.main = 'Y' AND tmob.tel_num LIKE '07%'
LEFT JOIN telephone twork FROM telephone ON twork.mem_id = members.mem_id AND twork.player = 'Y'
WHERE ...
Таблица участников:
memid | mname | reg
---------------------------------
22 | george | 1456436456
Телефонный стол:
tel_id | mem_id | tel_name | tel_num | main | player | home
-----------------------------------------------------------------------
1 | 22 | Home phone | 01234 456463 | N | N | Y
12 | 22 | Work phone | 01334 457893 | Y | N | N
19 | 22 | Mobile num | 07564 456333 | N | Y | N
23 | 22 | Home phone | 01987 657353 | N | N | N
Желаемый результат:
memid | mname | reg | twork | tmob | thome | etc...
-------------------------------------------------------------------
22 | george | 1456436456 | 01334...| 07564...| 01234...| ...
Я знаю, что столбцы флага (main
, player
и т.д.) Могут быть ENUM
Я также знаю, что таблицы могут быть InnoDB и иметь внешние ключи.
(Я вовсе не против любой из этих вещей, это просто случай обхода их)
Ни один из телефонных номеров не требуется, поэтому, когда я знаю, INNER JOIN
более эффективен; он не может быть использован в этом случае.
GROUP_CONCAT
не работает на основе столбцов, так как каждый столбец выбирается критериями разности (флаги). Возможно, он может работать в более широких настройках, но каждое значение tel_num
нужно будет переименовать (я думаю, что я мог бы использовать некоторые рекомендации по использованию GROUP_CONCAT
в этой ситуации).
Флаги не являются взаимоисключающими: номер телефона может быть | Y | Y | Y
| Y | Y | Y
| Y | Y | Y
Но каждый член имеет только один из каждого флага.
Все номера телефонов активны, некоторые из них не имеют флагов и поэтому их не нужно возвращать.
Вопрос:
LEFT JOIN
s?Другие вопросы, на которые я смотрел, имеют похожие проблемы:
Без изменения текущей схемы я не вижу способа улучшить ваш текущий запрос, кроме, возможно, пытается настроить его с помощью индексов и т.д. Однако я могу внести предлагаемое изменение в ваш дизайн таблицы, который может окунуться в гораздо более простой запрос нужного результата. Рассмотрим рефакторинг telephone
стола следующим образом:
tel_id | mem_id | tel_name | tel_num | type | active
-----------------------------------------------------------------------
1 | 22 | Home phone | 01234 456463 | 1 | 1
12 | 22 | Work phone | 01334 457893 | 2 | 1
19 | 22 | Mobile num | 07564 456333 | 3 | 1
23 | 22 | Home phone | 01987 657353 | 1 | 0
Теперь, если вам нужен запрос, который будет получать активные домашние, рабочие и мобильные номера для каждого члена, должно быть достаточно:
SELECT
m.memid,
m.mname,
m.reg,
GROUP_CONCAT(t.tel_name ORDER BY t.type) names,
GROUP_CONCAT(t.tel_num ORDER BY t.type) numbers
FROM members m
LEFT JOIN telephone t
ON m.mem_id = t.mem_id AND
t.active = 1
GROUP BY
m.memid;
Я предположил, что каждый член всегда будет иметь запись для домашних, рабочих и мобильных номеров, даже если эта запись должна быть пустой или пустой. Я также предполагаю, что вы хотите только сообщить активные номера. Если вам нужно сообщить обо всех числах, то решение группы concat станет менее привлекательным, потому что вы можете вернуть длинную строку CSV, и это может быть неинтуитивно, как читать.
Редактировать:
Мы также можем GROUP_CONCAT
называть названия телефонов. Это означает, что каждая запись в наборе результатов будет содержать в дополнение к списку номеров CSV соответствующие типы номеров телефонов.
ENUM
).type
(что, безусловно, будет проблемой для некоторых членов), это может привести к путанице в результатахGROUP_CONCAT
например, если у члена нет номераtype=2
, возвращаются типы 1 и 3, но показывается, как будто тип 1 и 2?