mysql - 实现下面的查询是否需要使用游标？

Question

考虑数据库:

• 一家公司拥有零个或多个分公司
• 一家公司有零封或多封电子邮件
• 一个分支机构有零个或多个电子邮件

<小时/>

并考虑该数据库的以下实例:

• 1 家公司，2 家分支机构
• 公司有 1 个电子邮件
• 每个分支机构有 2 个电子邮件

(共 5 封电子邮件)

如果您愿意，请查看graphical representation那个实例。

<小时/>

查询

我想进行一个查询 (MySQL) 来检索与给定公司相关的所有电子邮件(在示例中为 5)。我找不到解决方案，所以经过一番谷歌搜索后，我发现有一个叫做 cursor 的东西。 .

<小时/>

问题

• 上面的查询是否需要使用游标来实现？这会是一个好的解决方案吗？对于上述查询，您的解决方案是什么？

(表格定义如下)

<小时/>

我的想法是检索给定公司的所有分支机构(我可以进行查询)，但从那里我不知道如何迭代分支机构(查询的结果)并且获取所有电子邮件。

<小时/>

MySQL 中的表定义

-- Database tables
CREATE TABLE `company` (

        `c_id`          INT(11) NOT NULL AUTO_INCREMENT,
        `c_name`        VARCHAR(45) NOT NULL,

        PRIMARY KEY (`c_name`),
        UNIQUE KEY `c_id` (`c_id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8


CREATE TABLE `branch` (

        `b_id`          INT(11) NOT NULL AUTO_INCREMENT,
        `c_id`          INT(11) NOT NULL,
        `b_name` VARCHAR(45) NOT NULL,

        PRIMARY KEY (`b_name`),
        UNIQUE KEY `b_id` (`b_id`),
        KEY `c_id` (`c_id`),

        CONSTRAINT `branch_ibfk_1` FOREIGN KEY (`c_id`)
                REFERENCES `company` (`c_id`)
                ON DELETE CASCADE
                ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8


CREATE TABLE `email` (

        `e_id`          INT(11) NOT NULL AUTO_INCREMENT,
        `e_addr`        VARCHAR(45) NOT NULL,

        PRIMARY KEY (`e_addr`),
        UNIQUE KEY `e_id` (`e_id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8


CREATE TABLE `c_email` (

        `c_id`          INT(11) NOT NULL,
        `e_id`          INT(11) NOT NULL,

        PRIMARY KEY (`c_id`,`e_id`),
        KEY `e_id` (`e_id`),

        CONSTRAINT `c_email_ibfk_1` FOREIGN KEY (`c_id`)
                REFERENCES `company` (`c_id`)
                ON DELETE CASCADE
                ON UPDATE CASCADE,

        CONSTRAINT `c_email_ibfk_2` FOREIGN KEY (`e_id`)
                REFERENCES `email` (`e_id`)
                ON DELETE CASCADE
                ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE `b_email` (

        `b_id`          INT(11) NOT NULL,
        `e_id`          INT(11) NOT NULL,

        PRIMARY KEY (`b_id`,`e_id`),
        KEY `e_id` (`e_id`),

        CONSTRAINT `b_email_ibfk_1` FOREIGN KEY (`b_id`)
                REFERENCES `branch` (`b_id`)
                ON DELETE CASCADE
                ON UPDATE CASCADE,

        CONSTRAINT `b_email_ibfk_2` FOREIGN KEY (`e_id`)
                REFERENCES `email` (`e_id`)    
                ON DELETE CASCADE
                ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8


-- Example of query
-- Retrieving all e-mails of a given branch
SELECT e_addr
        FROM email      AS _em
        JOIN b_email    AS _be ON _be.e_id = _em.e_id
        JOIN branch     AS _br ON _br.b_id = _be.b_id
        WHERE b_name = @bra2;

-- How to retrieve all e-mails related to a given company
-- (e-mails from the company itself and also from all of its branches)?
-- ?

Answer 1

不，您不需要为此使用光标。您可以使用 SQL 来完成此操作——比游标好得多。它是这样的:

select *
from ((select ce.c_id, e.email
       from c_email ce join
            email e
            on e.e_id = ce.e_id
      ) union all
      (select b.c_id, e.email
       from branch b join
            b_email be
            on b.b_id = be.b_id join
            email e
            on be.e_id = e.e_id
      )
     ) e
where ce.c_id = X;

编辑:

您可以将 b_id 添加到第二个子查询中，但不能添加到第一个子查询中。也许你可以这样做:

select *
from ((select ce.c_id, e.email, NULL as b_id
       from c_email ce join
            email e
            on e.e_id = ce.e_id
      ) union all
      (select b.c_id, e.email, b.b_id
       from branch b join
            b_email be
            on b.b_id = be.b_id join
            email e
            on be.e_id = e.e_id
      )
     ) e
where ce.c_id = X
order by c_id, b_id;