一。介绍

我正在构建一个支持系统,其中来自某个国家/地区的用户会提出某个类别的问题,然后会分配该国家/地区,行政区划和类别的专家。

前任。邮政编码为Germany的国家1000的用户提出了有关Software类别的问题。来自国家Germany和/或具有邮政编码边界MIN_PROVINCE_ZIPCODE <= 1000 >= MAX_PROVINCE_ZIPCODE的省和/或具有邮政编码边界MIN_REGION_ZIPCODE <= 1000 >= MAX_REGION_ZIPCODE和类别Software的区域的专家被分配了此问题。

IE。:
选择所有发行国家/地区等于专家国家/地区,发行类别等于专家类别之一,和/或发行邮政编码大于或等于最小省邮政编码而小于或等于最大省邮政编码的所有发行,以及/或发布的邮政编码大于或等于区域最小邮政编码,并且发布的代码小于或等于邮政编码。

“和/或”是指是否指派专家从特定的行政部门处理问题,如果不是,则为他们分配与他们的国家和类别相匹配的所有内容

二。数据库架构和记录

*请紧记!*

a)专家可以成为...的一部分

  • 一个,多个或没有类别
  • 一个,多个或没有省份
  • 一个,多个或无区域

  • b)如果专家不是...的而不是的一部分...
  • 一个类别,那么将没有问题分配给他们
  • 一个省,然后将其国家和类别的所有问题分配给他们
  • 一个地区,然后将其省的所有问题分配给他们

  • 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 |
    +----------+----------+
    

    三,解决方案

    我设法提出了一半的解决方案。

    1.我的一半解决方案

    一个问题:

    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)手动固定的结果进行比较,我们可以注意到...
  • issue_id3可以而不是分配给expert_id1,因为issue_id1来自国家Germany,就像专家一样,也被分配在category_id2上,就像专家 一样,并且1500编号expert_id被分配为仅从该1中的issues编号province_id4编号regions_id8中获取10。因此,province邮政编码的范围从regions2000,从22992600,而2699邮政编码不属于其中。
  • issueissue_id可以分配给1expert_id,因为1issue_id是来自国家1的,就像专家一样,也是在Germanycategory_id上分配的,就像专家一样,zipt 2也在边界之间内的2100编号province_id4编号region_id的值,该值由8编号expert_id覆盖。
  • 1编号issue_id可以而不是分配给4编号expert_id,因为1编号issue_id来自国家4,就像专家一样,在Germany编号category_id上分配,在jQueryt_strong中但在jQueryt_strong内42800的边界,但不在province_id4region_id的边界内,后者被分配为810
  • 覆盖
  • expert_id1可以分配给issue_id2,因为expert_id2是来自国家issue_id的,就像专家一样,也是在2France上分配的,就像专家一样,邮政编码是category_id,它在边界内分配给此专家的11900中的。
  • province_id6可以分配给issue_id3,因为expert_id3来自国家issue_id,就像专家一样,也像专家一样在3Germany上分配。此外,该专家没有任何行政区划限制。也就是说,此专家可以从所有category_id
  • 中获取1
    因此,我们列出了所有可以分配给issuesGermany

    2.缺少一半解决方案

    如您所见,我的一半解决方案没有考虑到行政区划限制。
    我不能使用过程或 View 来实现此目的,但是,如果有帮助,我可以将其拆分为多个查询。
    数据库是MySQL(5.0.1-MySQL Community Server(GPL)),编程语言是PHP(5.1)。

    最佳答案

    我只是修改@krubo的答案。

    如果要子查询,查询将是:

    SELECT
        tis.id AS issue_id,
        tex.id AS expert_id,
        tis.categoryid AS issue_category_id,
        tex.categoryid AS expert_category_id,
        tis.country AS issue_country,
        tex.country AS expert_country,
        tis.zipcode AS issue_zipcode,
        tis.provinceid AS province_id,
        tis.province_minzipcode AS province_minzipcode,
        tis.province_maxzipcode AS province_maxzipcode,
        tis.regionid AS region_id,
        tis.region_minzipcode AS region_minzipcode,
        tis.region_maxzipcode AS region_maxzipcode
    FROM
    (
        SELECT
            i.id, categoryid, i.country, zipcode,
            provinces.id AS provinceid, provinces.min_zipcode AS province_minzipcode,
            provinces.max_zipcode AS province_maxzipcode, regions.id AS regionid,
            regions.min_zipcode AS region_minzipcode,
            regions.max_zipcode AS region_maxzipcode
        FROM
            issues AS i
        LEFT JOIN provinces ON i.country=provinces.country
          AND i.zipcode BETWEEN provinces.min_zipcode AND provinces.max_zipcode
        LEFT JOIN regions on provinces.id=regions.provinceid
          AND i.zipcode BETWEEN regions.min_zipcode AND regions.max_zipcode
    ) AS tis
    JOIN
    (
        SELECT
           e.id, country, categoryid, provinceid, regionid
        FROM
           experts e
        JOIN experts_categories ON e.id=experts_categories.expertid
        LEFT JOIN experts_provinces ON e.id=experts_provinces.expertid
        LEFT JOIN experts_regions ON e.id=experts_regions.expertid
    ) AS tex
    WHERE
        tis.country=tex.country
        AND tis.categoryid=tex.categoryid
        AND (tis.provinceid IS NULL
            OR tex.provinceid IS NULL
            OR tis.provinceid=tex.provinceid)
        AND (tis.regionid IS NULL
            OR tex.regionid IS NULL
            OR tis.regionid=tex.regionid);
    

    结果是:
    +----------+-----------+-------------------+--------------------+---------------+----------------+---------------+-------------+----------------------+----------------------+-----------+--------------------+--------------------+
    | 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 |
    +----------+-----------+-------------------+--------------------+---------------+----------------+---------------+-------------+----------------------+----------------------+-----------+--------------------+--------------------+
    |        1 |         1 |                 2 |                  2 | Germany       | Germany        |          2100 |           4 |                 2000 |                 2899 |         8 |               2000 |               2299 |
    |        2 |         2 |                 1 |                  1 | France        | France         |          1900 |           6 |                 2000 |                 2199 |        14 |               1800 |               2000 |
    |        3 |         3 |                 1 |                  1 | Germany       | Germany        |          1500 |           3 |                 2000 |                 1999 |         6 |               1500 |               1699 |
    

    关于php - MySQL多表联接,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/6317379/

    10-09 16:12