具有 5 个表的复杂 SQL 联接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14633547/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Complex SQL Join with 5 tables
提问by rekam
I'm working on a relatively huge application, which contains quite a lot of tables. I have to write a SQL query which involve, after simplification, 5 tables (see the jpg for the joins).
我正在开发一个相对庞大的应用程序，其中包含相当多的表。我必须编写一个 SQL 查询，简化后涉及 5 个表（请参阅 jpg 以了解连接）。
The idea is as follows: Persons have addresses, and addresses have a type (private, pro, etc) and a country. Persons can also have options. These options (illustrated here in the option table with a name_id) can be linked to an address type.
想法如下：人有地址，地址有类型（私人、专业等）和国家。人也可以有选择。这些选项（此处在带有 name_id 的选项表中说明）可以链接到地址类型。
The idea is to extract all the persons who have one or more addresses which are specified by a country AND by the fact that they also appear in the [option address] table.
这个想法是提取所有拥有一个或多个地址的人，这些地址由一个国家指定，并且他们也出现在 [option address] 表中。
For example, let's say we want the persons who have an address with country_id=1. The resultset must exclude the persons who don't have the same address type linked to their options.
例如，假设我们想要地址为 country_id=1 的人。结果集必须排除与其选项链接的地址类型不同的人员。
Well... I'm not sure I understand myself :)
嗯...我不确定我是否了解自己 :)
But anyway, here's the SQL to create all the stuff.
但无论如何，这是创建所有内容的 SQL。
CREATE TABLE `address` (
`person_id` int(11) NOT NULL,
`type_id` int(11) NOT NULL,
`country_id` int(11) NOT NULL,
UNIQUE KEY `apt` (`person_id`,`type_id`),
KEY `apid` (`person_id`),
KEY `atid` (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `address` (`person_id`, `type_id`, `country_id`) VALUES
(1, 1, 1),
(2, 2, 1),
(3, 1, 1),
(3, 2, 2),
(5, 1, 2),
(6, 2, 1),
(7, 1, 1),
(7, 2, 2),
(8, 1, 1),
(9, 2, 1);
CREATE TABLE `address_type` (
`id` int(11) NOT NULL,
UNIQUE KEY `tid` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `address_type` (`id`) VALUES
(1),
(2);
CREATE TABLE `option` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name_id` int(11) NOT NULL,
`person_id` int(11) NOT NULL,
UNIQUE KEY `oid` (`id`),
UNIQUE KEY `onp` (`name_id`,`person_id`),
KEY `opid` (`person_id`),
KEY `on` (`name_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
INSERT INTO `option` (`id`, `name_id`, `person_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 5),
(5, 1, 6),
(6, 1, 7),
(7, 1, 8),
(8, 1, 9);
CREATE TABLE `option_address_type` (
`option_id` int(11) NOT NULL,
`type_id` int(11) NOT NULL,
UNIQUE KEY `ot` (`option_id`,`type_id`),
KEY `ooid` (`option_id`),
KEY `otid` (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `option_address_type` (`option_id`, `type_id`) VALUES
(1, 1),
(2, 2),
(3, 1),
(3, 2),
(4, 2),
(5, 1),
(6, 1),
(7, 1),
(7, 2),
(8, 1),
(8, 2);
CREATE TABLE `person` (
`id` int(11) NOT NULL,
UNIQUE KEY `pid` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `person` (`id`) VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9);
ALTER TABLE `address`
ADD CONSTRAINT `address_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `address_ibfk_2` FOREIGN KEY (`type_id`) REFERENCES `address_type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `option`
ADD CONSTRAINT `option_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `option_address_type`
ADD CONSTRAINT `option_address_type_ibfk_1` FOREIGN KEY (`option_id`) REFERENCES `option` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `option_address_type_ibfk_2` FOREIGN KEY (`type_id`) REFERENCES `address_type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
回答by diederikh
What about this:
那这个呢：
select person_id
from address adr
, `option` opt
, option_address_type opt_adt
where adr.country_id = 1
and opt.person_id = adr.person_id
and opt_adt.option_id = opt.option_id
and opt_adt.type_id = adr.type_id
or
或者
select person_id
from address adr
inner join `option` opt
on opt.person_id = adr.person_id
and adr.country_id = 1
inner join option_address_type opt_adt
on opt_adt.option_id = opt.option_id
and opt_adt.type_id = adr.type_id
回答by sgeddes
I'm not sure I completely understand what your desired results are. When the country is 1, exclude the people where address type not equal to option address type?
我不确定我是否完全理解您想要的结果。当国家为1时，排除地址类型不等于选项地址类型的人？
Something like this?
像这样的东西？
SELECT p.id
FROM Person p
JOIN address a ON p.Id = a.Person_ID
JOIN address_type at ON a.Type_ID = at.Id
JOIN `option` o ON p.Id = o.person_Id
JOIN option_address_type oat ON o.id = oat.option_id
WHERE a.country_id = 1 AND at.id <> oat.type_id;
And the SQL Fiddle.
和SQL 小提琴。
Good luck.
祝你好运。
