Вопросы Теги

MySQL multiple соединения таблиц

I. Введение

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

E.x. Пользователь из страны Германия с почтовым индексом 1000 поднимает проблему в категории Программное обеспечение . Эксперты из страны Германия и / или провинции с границами почтового индекса MIN_PROVINCE_ZIPCODE <= 1000> = MAX_PROVINCE_ZIPCODE и / или региона с границами почтового индекса MIN_REGION_ZIPCODE <1000> = MAX_REGION_ZIPCODE и категории Программное обеспечение назначаются проблеме.

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

"и / или" означает, если эксперты назначены для рассмотрения вопросов из конкретного административного подразделения (ов), если это не так, затем присвойте им все, что соответствует их стране и категории

II. Схемы и записи базы данных

* Имейте в виду! *

a) Эксперты могут входить в ...

  1. одну, несколько категорий или ни одной категории
  2. одну, несколько или ни одной провинции ( s)
  3. один, несколько или ни одного региона (ов)

b) Если эксперты НЕ входят в ...

  1. категорию, то им не будут назначены никакие проблемы
  2. провинция, то им будут назначены все проблемы для их страны и категории
  3. регион, затем им будут назначены все проблемы для их провинции (ов)

1. Схемы 

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(300) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `provinces` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(300) NOT NULL,
  `province` varchar(300) NOT NULL,
  `min_zipcode` int(5) unsigned NOT NULL,
  `max_zipcode` int(5) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `regions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `provinceid` int(11) unsigned NOT NULL,
  `region` varchar(300) NOT NULL,
  `min_zipcode` int(5) unsigned NOT NULL,
  `max_zipcode` int(5) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `issues` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `categoryid` int(11) unsigned NOT NULL,
  `country` varchar(150) NOT NULL,
  `zipcode` int(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `experts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(150) NOT NULL DEFAULT 'none',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `experts_categories` (
  `expertid` int(11) unsigned NOT NULL,
  `categoryid` int(11) unsigned NOT NULL,
  PRIMARY KEY (`expertid`,`categoryid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `experts_provinces` (
  `expertid` int(11) unsigned NOT NULL,
  `provinceid` int(11) unsigned NOT NULL,
  PRIMARY KEY (`expertid`,`provinceid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `experts_regions` (
  `expertid` int(11) NOT NULL,
  `regionid` int(11) NOT NULL,
  PRIMARY KEY (`expertid`,`regionid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2. Записи 

INSERT INTO `categories` (`id`, `name`) VALUES
(1, 'Software'),
(2, 'Hardware');

INSERT INTO `experts` (`id`, `country`) VALUES
(1, 'Germany'),
(2, 'France'),
(3, 'Germany');

INSERT INTO `experts_categories` (`expertid`, `categoryid`) VALUES
(1, 1),
(1, 2),
(2, 1),
(3, 1);

INSERT INTO `experts_provinces` (`expertid`, `provinceid`) VALUES
(1, 4),
(2, 6),
(2, 7);

INSERT INTO `experts_regions` (`expertid`, `regionid`) VALUES
(1, 8),
(1, 10);

INSERT INTO `issues` (`id`, `categoryid`, `country`, `zipcode`) VALUES
(1, 2, 'Germany', 2100),
(2, 1, 'France', 1900),
(3, 1, 'Germany', 1500),
(4, 2, 'Germany', 2800),
(5, 2, 'France', 1850);

INSERT INTO `provinces` (`id`, `country`, `province`, `min_zipcode`, `max_zipcode`) VALUES
(1, 'Germany', 'Province One', 1000, 1299),
(2, 'Germany', 'Province Two', 1300, 1499),
(3, 'Germany', 'Province Three', 1500, 1999),
(4, 'Germany', 'Province Four', 2000, 2899),
(5, 'France', 'Province One', 1000, 1799),
(6, 'France', 'Province Two', 1800, 2199),
(7, 'France', 'Province Three', 2200, 2399);

INSERT INTO `regions` (`id`, `provinceid`, `region`, `min_zipcode`, `max_zipcode`) VALUES
(1, 1, 'Region One', 1000, 1099),
(2, 1, 'Region Two', 1100, 1159),
(3, 1, 'Region Three', 1160, 1299),
(4, 2, 'Region One', 1300, 1400),
(5, 2, 'Region Two', 1401, 1499),
(6, 3, 'Region One', 1500, 1699),
(7, 3, 'Region Two', 1700, 1999),
(8, 4, 'Region One', 2000, 2299),
(9, 4, 'Region Two', 2300, 2599),
(10, 4, 'Region Three', 2600, 2699),
(11, 4, 'Region Four', 2700, 2899),
(12, 5, 'Region One', 1000, 1699),
(13, 5, 'Region Two', 1700, 1799),
(14, 6, 'Region One', 1800, 2000),
(15, 6, 'Region Two', 2001, 2199),
(16, 7, 'Region One', 2200, 2299),
(17, 7, 'Region Two', 2300, 2399);

3. Визуальные схемы 

mysql> DESC `categories`;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(300)     | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

mysql> DESC `provinces`;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(11) unsigned | NO   | PRI | NULL    | auto_increment |
| country     | varchar(300)        | NO   |     | NULL    |                |
| province    | varchar(300)        | NO   |     | NULL    |                |
| min_zipcode | int(5) unsigned     | NO   |     | NULL    |                |
| max_zipcode | int(5) unsigned     | NO   |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

mysql> DESC `regions`;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| provinceid  | int(11) unsigned | NO   |     | NULL    |                |
| region      | varchar(300)     | NO   |     | NULL    |                |
| min_zipcode | int(5) unsigned  | NO   |     | NULL    |                |
| max_zipcode | int(5) unsigned  | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

mysql> DESC `issues`;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(11) unsigned | NO   | PRI | NULL    | auto_increment |
| categoryid | int(11) unsigned    | NO   |     | NULL    |                |
| country    | varchar(150)        | NO   |     | NULL    |                |
| zipcode    | int(5)              | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

mysql> DESC `experts`;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| country | varchar(150)     | NO   |     | none    |                |
+---------+------------------+------+-----+---------+----------------+

mysql> DESC `experts_categories`;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| expertid   | int(11) unsigned | NO   | PRI | NULL    |       |
| categoryid | int(11) unsigned | NO   | PRI | NULL    |       |
+------------+------------------+------+-----+---------+-------+

mysql> DESC `experts_provinces`;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| expertid   | int(11) unsigned | NO   | PRI | NULL    |       |
| provinceid | int(11) unsigned | NO   | PRI | NULL    |       |
+------------+------------------+------+-----+---------+-------+

mysql> DESC `experts_regions`;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| expertid | int(11) | NO   | PRI | NULL    |       |
| regionid | int(11) | NO   | PRI | NULL    |       |
+----------+---------+------+-----+---------+-------+

4. Визуальные записи 

mysql> SELECT * FROM `categories`;
+----+----------+
| id | name     |
+----+----------+
|  1 | Software |
|  2 | Hardware |
+----+----------+

mysql> SELECT * FROM `provinces`;
+----+---------+----------------+-------------+-------------+
| id | country | province       | min_zipcode | max_zipcode |
+----+---------+----------------+-------------+-------------+
|  1 | Germany | Province One   |        1000 |        1299 |
|  2 | Germany | Province Two   |        1300 |        1499 |
|  3 | Germany | Province Three |        1500 |        1999 |
|  4 | Germany | Province Four  |        2000 |        2899 |
|  5 | France  | Province One   |        1000 |        1799 |
|  6 | France  | Province Two   |        1800 |        2199 |
|  7 | France  | Province Three |        2200 |        2399 |
+----+---------+----------------+-------------+-------------+

mysql> SELECT * FROM `regions`;
+----+------------+--------------+-------------+-------------+
| id | provinceid | region       | min_zipcode | max_zipcode |
+----+------------+--------------+-------------+-------------+
|  1 |          1 | Region One   |        1000 |        1099 |
|  2 |          1 | Region Two   |        1100 |        1159 |
|  3 |          1 | Region Three |        1160 |        1299 |
|  4 |          2 | Region One   |        1300 |        1400 |
|  5 |          2 | Region Two   |        1401 |        1499 |
|  6 |          3 | Region One   |        1500 |        1699 |
|  7 |          3 | Region Two   |        1700 |        1999 |
|  8 |          4 | Region One   |        2000 |        2299 |
|  9 |          4 | Region Two   |        2300 |        2599 |
| 10 |          4 | Region Three |        2600 |        2699 |
| 11 |          4 | Region Four  |        2700 |        2899 |
| 12 |          5 | Region One   |        1000 |        1699 |
| 13 |          5 | Region Two   |        1700 |        1799 |
| 14 |          6 | Region One   |        1800 |        2000 |
| 15 |          6 | Region Two   |        2001 |        2199 |
| 16 |          7 | Region One   |        2200 |        2299 |
| 17 |          7 | Region Two   |        2300 |        2399 |
+----+------------+--------------+-------------+-------------+

mysql> SELECT * FROM `issues`;
+----+------------+---------+---------+
| id | categoryid | country | zipcode |
+----+------------+---------+---------+
|  1 |          2 | Germany |    2100 |
|  2 |          1 | France  |    1900 |
|  3 |          1 | Germany |    1500 |
|  4 |          2 | Germany |    2800 |
|  5 |          2 | France  |    1850 |
+----+------------+---------+---------+

mysql> SELECT * FROM `experts`;
+----+---------+
| id | country |
+----+---------+
|  1 | Germany |
|  2 | France  |
|  3 | Germany |
+----+---------+

mysql> SELECT * FROM `experts_categories`;
+----------+------------+
| expertid | categoryid |
+----------+------------+
|        1 |          1 |
|        1 |          2 |
|        2 |          1 |
|        3 |          1 |
+----------+------------+

mysql> SELECT * FROM `experts_provinces`;
+----------+------------+
| expertid | provinceid |
+----------+------------+
|        1 |          4 |
|        2 |          6 |
|        2 |          7 |
+----------+------------+

mysql> SELECT * FROM `experts_regions`;
+----------+----------+
| expertid | regionid |
+----------+----------+
|        1 |        8 |
|        1 |       10 |
+----------+----------+

III. Решение

Мне удалось найти половину решения.

1. Мое половинное решение

a) Запрос: 

SELECT 
        `i`.`id` `issue_id`, 
        `e`.`id` `expert_id`
FROM `issues` `i` 
INNER JOIN `experts` `e` 
        ON `i`.`country` = `e`.`country` 
INNER JOIN `experts_categories` `ec` 
        ON `e`.`id` = `ec`.`expertid` 
        AND `i`.`categoryid` = `ec`.`categoryid`
ORDER BY `e`.`id`, `ec`.`categoryid` ASC

b) Результат: 

+----------+-----------+
| issue_id | expert_id |
+----------+-----------+
|        3 |         1 |
|        1 |         1 |
|        4 |         1 |
|        2 |         2 |
|        3 |         3 |
+----------+-----------+

c) Точный результат был бы: 

+----------+-----------+
| issue_id | expert_id |
+----------+-----------+
|        1 |         1 |
|        2 |         2 |
|        3 |         3 |
+----------+-----------+

Объяснение того, почему приведенный выше визуальный результат является точным.

Во-первых, давайте получим "полное соединение", чтобы мы могли проводить сравнения:
d) Запрос: 

SELECT 
        `i`.`id` `issue_id`, `e`.`id` `expert_id`, `i`.`categoryid` `issue_category_id`, `ec`.`categoryid` `expert_category_id`, 
        `i`.`country` `issue_country`, `e`.`country` `expert_country`,
        `i`.`zipcode` `issue_zipcode`,
        `p`.`id` `province_id`, `p`.`min_zipcode` `province_min_zipcode`, `p`.`max_zipcode` `province_max_zipcode`,
        `r`.`id` `region_id`, `r`.`min_zipcode` `region_min_zipcode`, `r`.`max_zipcode` `region_max_zipcode`
FROM `issues` `i`
INNER JOIN `experts` `e` 
        ON `i`.`country` = `e`.`country`
INNER JOIN `experts_categories` `ec` 
        ON `ec`.`expertid` = `e`.`id`
        AND `i`.`categoryid` = `ec`.`categoryid`
LEFT JOIN `experts_provinces` `ep`
        ON `e`.`id` = `ep`.`expertid`
LEFT JOIN `provinces` `p`
        ON `ep`.`provinceid` = `p`.`id`
LEFT JOIN `experts_regions` `er`
        ON `e`.`id` = `er`.`expertid`
LEFT JOIN `regions` `r`
        ON `er`.`regionid` = `r`.`id`
        AND `p`.`id` = `r`.`provinceid`
ORDER BY `e`.`id`,`ec`.`categoryid` ASC

e) Результат: 

+----------+-----------+-------------------+--------------------+---------------+----------------+---------------+-------------+----------------------+----------------------+-----------+--------------------+--------------------+
| issue_id | expert_id | issue_category_id | expert_category_id | issue_country | expert_country | issue_zipcode | province_id | province_min_zipcode | province_max_zipcode | region_id | region_min_zipcode | region_max_zipcode |
+----------+-----------+-------------------+--------------------+---------------+----------------+---------------+-------------+----------------------+----------------------+-----------+--------------------+--------------------+
|        3 |         1 |                 1 |                  1 | Germany       | Germany        |          1500 |           4 |                 2000 |                 2899 |        10 |               2600 |               2699 |
|        3 |         1 |                 1 |                  1 | Germany       | Germany        |          1500 |           4 |                 2000 |                 2899 |         8 |               2000 |               2299 |
|        1 |         1 |                 2 |                  2 | Germany       | Germany        |          2100 |           4 |                 2000 |                 2899 |        10 |               2600 |               2699 |
|        1 |         1 |                 2 |                  2 | Germany       | Germany        |          2100 |           4 |                 2000 |                 2899 |         8 |               2000 |               2299 |
|        4 |         1 |                 2 |                  2 | Germany       | Germany        |          2800 |           4 |                 2000 |                 2899 |        10 |               2600 |               2699 |
|        4 |         1 |                 2 |                  2 | Germany       | Germany        |          2800 |           4 |                 2000 |                 2899 |         8 |               2000 |               2299 |
|        2 |         2 |                 1 |                  1 | France        | France         |          1900 |           7 |                 2200 |                 2399 |      NULL |               NULL |               NULL |
|        2 |         2 |                 1 |                  1 | France        | France         |          1900 |           6 |                 1800 |                 2199 |      NULL |               NULL |               NULL |
|        3 |         3 |                 1 |                  1 | Germany       | Germany        |          1500 |        NULL |                 NULL |                 NULL |      NULL |               NULL |               NULL |
+----------+-----------+-------------------+--------------------+---------------+----------------+---------------+-------------+----------------------+----------------------+-----------+--------------------+--------------------+

Таким образом, сравнивая результат запроса (b) с результатом (c), зафиксированным вручную результатом, мы можем заметить ...

  1. issue_id number 3 НЕ МОЖЕТ назначать expert_id number 1 , потому что issue_id номер 1 из страны Германия , как и эксперту, присваивается category_id number 2 , как и эксперту, НО имеет почтовый индекс 1500 , а expert_id число 1 назначается для получения проблем только из . идентификатор_провинции номер 4 и идентификатор_региона номер 8 и 10 в пределах этой провинции . Таким образом, почтовые индексы регионов варьируются от 2000 до 2299 и от 2600 до 2699 , где наш ] номер выпуска не принадлежит.
  2. идентификатор_выпуска номер 1 может быть назначен на идентификатор_эксперта номер 1 , потому что issue_id номер 1 из страны Германия , как и эксперт, назначается на category_id номер 2 , как и эксперт имеет почтовый индекс 2100 , который находится между границами Province_id числа 4 и region_id числа 8 в пределах провинции, для которой назначено покрытие expert_id number 1 .
  3. issue_id number 4 can НЕ [11119 020] присваивается expert_id номер 1 , потому что issue_id номер 4 из страны Германия , как и эксперту присваивается category_id номер 4 , НО имеет почтовый индекс 2800 , который находится в пределах Province_id номер 4 , но это не в пределах region_id number 8 и 10 , которому присвоено покрытие expert_id number 1
  4. issue_id number 2 может быть назначено на expert_id номер 2 , потому что issue_id number 2 из страны Франция , как и эксперт, назначен на category_id [1 1119070] номер 1 , как и эксперт, имеет почтовый индекс 1900 , который находится в пределах Province_id числа 6 , назначенный для этого эксперта.
  5. issue_id number 3 может быть присвоен expert_id number 3 , потому что issue_id номер 3 из страны Германия , как и эксперт, назначается по category_id number 1 , как и эксперт . Кроме того, у этого эксперта нет ограничений по административному делению. То есть этот эксперт может взять проблем из всех Германии

Таким образом, мы перечислили все проблемы , которые могут быть переданы экспертам .

2. Отсутствует половинное решение

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

База данных - это MySQL (5.0.1 - MySQL Community Server (GPL)) и язык программирования это PHP (5.1).

6
задан Shef 11 June 2011 в 18:56
поделиться