gpt4 book ai didi

sql - 具有 5 个表的复杂 SQL 连接

转载 作者:行者123 更新时间:2023-12-01 03:59:14 24 4
gpt4 key购买 nike

我正在开发一个相对庞大的应用程序,其中包含很多表。我必须编写一个 SQL 查询,简化后涉及 5 个表(请参阅连接的 jpg)。

思路如下:
人有地址,地址有类型(私有(private)、专业等)和国家。
人们也可以有选择。这些选项(在选项表中以 name_id 说明)可以链接到地址类型。

这个想法是提取所有拥有一个或多个地址的人,这些地址由一个国家指定,并且他们也出现在 [option address] 表中。

例如,假设我们想要地址为 country_id=1 的人。结果集必须排除那些没有与其选项相关联的相同地址类型的人。

嗯......我不确定我是否了解自己:)

但无论如何,这里是创建所有东西的 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;

最佳答案

那这个呢:

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

或者
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

关于sql - 具有 5 个表的复杂 SQL 连接,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14633547/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com