mysql - 帮助进行具有多个连接的 MySQL 查询

Question

设置:使用 4 个表联系数据库

• 联系人
• 城市
• 州
• zipper

结构:

CREATE TABLE `contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `first` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `prefix` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `suffix` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `address` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `address_1` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `city_id` int(100) DEFAULT NULL,
  `state_id` int(20) DEFAULT NULL,
  `alt_address_1` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `alt_address_2` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `alt_city` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `alt_state` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `alt_zip` varchar(15) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `publish_name` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `salutation` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `mail_label` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `solicitor` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `is_volunteer` tinyint(1) DEFAULT NULL,
  `is_sponsor` tinyint(1) DEFAULT '0',
  `is_company` tinyint(1) DEFAULT '0',
  `is_foundation` tinyint(1) DEFAULT '0',
  `status` varchar(15) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `created_on` datetime NOT NULL,
  `created_by` varchar(30) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `modified_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `agency_id` int(25) DEFAULT NULL,
  `primary_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `primary_id` (`primary_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3008 DEFAULT CHARSET=utf8

CREATE TABLE `cities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `stateid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `city` (`city`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8

CREATE TABLE `states` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `abbreviation` varchar(2) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `state` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `state` (`state`),
  UNIQUE KEY `abbreviation` (`abbreviation`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8

CREATE TABLE `zips` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `zip` varchar(10) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `cityid` int(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `zip` (`zip`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8

我已经用 111 个联系人填充了联系人，州只是所有州，城市有与州 ID 相关的相应 id 键，邮政编码有一个与城市匹配的键。

该查询是生成一个人员列表以匹配正确的字段。这是查询。

SELECT concat(contacts.last,' ', contacts.first) as name
     , cities.city
     , zips.zip
  FROM contacts
  JOIN cities
    ON cities.id = contacts.city_id
  JOIN states ON states.id = contacts.state_id
  JOIN zips ON zips.cityid = cities.id

此查询返回 338 行，可能有 11 个联系人。有明显的重复。当我加入邮政编码时会发生这种情况，因为它们属于多个城市，所以它会与每个城市匹配(我认为这就是发生的情况)。有人知道如何正确加入这些表吗？

谢谢。丰富

Answer 1

我认为您应该重新考虑许多这些表上的代理键用法，并尽可能使用自然键。以州表为例，在大多数情况下，简单地使用州短(即 TX 与 Texas)来实现数据和显示目的是可以接受的。这意味着，如果您删除状态表上的递增 ID 并为每个状态使用自然键，则在 90% 的情况下将减少联接的必要性。

然后在需要存储状态值的表中使用state.abbriviation作为FK。将其扩展到邮政编码和城市，您可以将州缩写 FK 到城市表，并从城市表到联系人表创建复合 FK，同时为您提供城市和州的 key 。

示例架构(排除邮政编码表和缩短的联系人表):

CREATE  TABLE IF NOT EXISTS `states` (
  `state_id` CHAR(2) NOT NULL ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`state_id`) ,
  UNIQUE INDEX `state_name` (`name` ASC)
)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `cities` (
  `state_id` CHAR(2) NOT NULL ,
  `city_name` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`state_id`, `city_name`) ,
  INDEX `fk_city_state_id` (`state_id` ASC) ,
  CONSTRAINT `fk_city_state_id`
    FOREIGN KEY (`state_id` )
    REFERENCES `states` (`state_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `contacts` (
  `contacts_id` INT NOT NULL AUTO_INCREMENT ,
  `state` CHAR(2) NULL ,
  `city` VARCHAR(255) NULL ,
  PRIMARY KEY (`contacts_id`) ,
  INDEX `fk_contact_city` (`state` ASC, `city` ASC) ,
  INDEX `fk_contact_state` (`state` ASC) ,
  CONSTRAINT `fk_contact_city`
    FOREIGN KEY (`state` , `city` )
    REFERENCES `cities` (`state_id` , `city_name` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_contact_state`
    FOREIGN KEY (`state` )
    REFERENCES `states` (`state_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Data for table `states`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO `states` (`state_id`, `name`) VALUES ('TX', 'Texas');
INSERT INTO `states` (`state_id`, `name`) VALUES ('CA', 'California');
INSERT INTO `states` (`state_id`, `name`) VALUES ('OR', 'Oregon');
COMMIT;

-- -----------------------------------------------------
-- Data for table `cities`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO `cities` (`state_id`, `city_name`) VALUES ('CA', 'modesto');
INSERT INTO `cities` (`state_id`, `city_name`) VALUES ('OR', 'protland');
INSERT INTO `cities` (`state_id`, `city_name`) VALUES ('TX', 'Dallas');
COMMIT;

现在您的查询已得到简化，除非在极端情况下您需要完整的状态命名法:

SELECT
    concat(contacts.last,' ', contacts.first) as name,
    city,
    state,
    zip
FROM contacts
WHERE {INSERTWHERE}