mysql - 如何通过泛化连接表

Question

我在对表执行 JOIN 时遇到问题，问题是:

在报告车系统中，我有用户，例如学生、家长和学校员工。我需要生成一条 SQL 语句，当我输入家长的访问 ID 时，它会列出与家长 ID 相关的所有学生

跟随模型:

这是实现这种“泛化”和家长与学生之间这种关系的最佳方式吗，因为他们都是用户？有人可以帮助我吗？

SQL 代码:

        -- -----------------------------------------------------
        -- Table `testeboletim`.`type_user`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `testeboletim`.`type_user` (
          `idtype_user` INT NOT NULL AUTO_INCREMENT,
          `role` VARCHAR(45) NULL,
          PRIMARY KEY (`idtype_user`))
        ENGINE = InnoDB;


        -- -----------------------------------------------------
        -- Table `testeboletim`.`user`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `testeboletim`.`user` (
          `iduser` INT NOT NULL AUTO_INCREMENT,
          `name` VARCHAR(45) NULL,
          `ID` VARCHAR(20) NULL,
          `birth` DATE NULL,
          `telephone` VARCHAR(20) NULL,
          `phone` VARCHAR(20) NULL,
          `email` VARCHAR(45) NULL,
          `type_user_idtype_user` INT NOT NULL,
          PRIMARY KEY (`iduser`, `type_user_idtype_user`),
          INDEX `fk_usuario_tipo_usuario_idx` (`type_user_idtype_user` ASC),
          CONSTRAINT `fk_usuario_tipo_usuario`
            FOREIGN KEY (`type_user_idtype_user`)
            REFERENCES `testeboletim`.`type_user` (`idtype_user`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB;


        -- -----------------------------------------------------
        -- Table `testeboletim`.`student`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `testeboletim`.`student` (
          `idstudent` INT NOT NULL AUTO_INCREMENT,
          `user_iduser` INT NOT NULL,
          `user_type_user_idtype_user` INT NOT NULL,
          PRIMARY KEY (`idstudent`, `user_iduser`, `user_type_user_idtype_user`),
          INDEX `fk_aluno_usuario1_idx` (`user_iduser` ASC, `user_type_user_idtype_user` ASC),
          CONSTRAINT `fk_aluno_usuario1`
            FOREIGN KEY (`user_iduser` , `user_type_user_idtype_user`)
            REFERENCES `testeboletim`.`user` (`iduser` , `type_user_idtype_user`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB;


        -- -----------------------------------------------------
        -- Table `testeboletim`.`parents`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `testeboletim`.`parents` (
          `idparents` INT NOT NULL AUTO_INCREMENT,
          `user_iduser` INT NOT NULL,
          `user_type_user_idtype_user` INT NOT NULL,
          PRIMARY KEY (`idparents`, `user_iduser`, `user_type_user_idtype_user`),
          INDEX `fk_responsavel_usuario1_idx` (`user_iduser` ASC, `user_type_user_idtype_user` ASC),
          CONSTRAINT `fk_responsavel_usuario1`
            FOREIGN KEY (`user_iduser` , `user_type_user_idtype_user`)
            REFERENCES `testeboletim`.`user` (`iduser` , `type_user_idtype_user`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB;


        -- -----------------------------------------------------
        -- Table `testeboletim`.`student_has_parents`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `testeboletim`.`student_has_parents` (
          `student_idstudent` INT NOT NULL,
          `student_user_iduser` INT NOT NULL,
          `parents_idparents` INT NOT NULL,
          `parents_user_iduser` INT NOT NULL,
          PRIMARY KEY (`student_idstudent`, `student_user_iduser`, `parents_idparents`, `parents_user_iduser`),
          INDEX `fk_aluno_has_responsavel_responsavel1_idx` (`parents_idparents` ASC, `parents_user_iduser` ASC),
          INDEX `fk_aluno_has_responsavel_aluno1_idx` (`student_idstudent` ASC, `student_user_iduser` ASC),
          CONSTRAINT `fk_aluno_has_responsavel_aluno1`
            FOREIGN KEY (`student_idstudent` , `student_user_iduser`)
            REFERENCES `testeboletim`.`student` (`idstudent` , `user_iduser`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `fk_aluno_has_responsavel_responsavel1`
            FOREIGN KEY (`parents_idparents` , `parents_user_iduser`)
            REFERENCES `testeboletim`.`parents` (`idparents` , `user_iduser`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB;


        SET SQL_MODE=@OLD_SQL_MODE;
        SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
        SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Answer 1

为了解决这个问题，我们需要查看testeboletim.student, testeboletim.parents，以及 testeboletim.student_has_parents 或 testeboletim.user。我已决定使用 testeboletim.user 来解决您的问题，因为它在引用键方面更清晰，但不是。

解决方案使用 testeboletim.user

根据您的问题，我们正在寻找 testeboletim.student 中具有相应 iduser testeboletim< 的所有行.user 基于 testeboletim.parents 的 user_iduser。

-- SQL 定义:

SELECT * FROM `testeboletim`.`student` WHERE `user_iduser` IN 
        (SELECT DISTINCT(`iduser`) FROM `testeboletim`.`user` WHERE `iduser` IN
                (SELECT DISTINCT(`user_iduser`) FROM `testeboletim`.`parents`)
        );

现在用JOIN做同样的事情，需要使用LEFT JOIN；在这种情况下 testeboletim.student。

SELECT * FROM `testeboletim`.`student` AS `student`
LEFT JOIN `testeboletim`.`user` AS `user` 
ON `student`.`user_iduser` = `user`.`iduser`
LEFT JOIN `testeboletim`.`parents` AS `parents`
ON `user`.`iduser` = `parents`.`user_iduser`;

因为我没有任何值，所以我将与您分享解释，以“证明”查询有效。

mysql>  SELECT * FROM `testeboletim`.`student` AS `student`
    ->     LEFT JOIN `testeboletim`.`user` AS `user` 
    ->     ON `student`.`user_iduser` = `user`.`iduser`
    ->     LEFT JOIN `testeboletim`.`parents` AS `parents`
    ->     ON `user`.`iduser` = `parents`.`user_iduser`;
Empty set (0.01 sec)

mysql> EXPLAIN  SELECT * FROM `testeboletim`.`student` AS `student`
    ->     LEFT JOIN `testeboletim`.`user` AS `user` 
    ->     ON `student`.`user_iduser` = `user`.`iduser`
    ->     LEFT JOIN `testeboletim`.`parents` AS `parents`
    ->     ON `user`.`iduser` = `parents`.`user_iduser`;
+------+-------------+---------+-------+-----------------------------+-----------------------------+---------+--------------------------+------+-------------------------------------------------+
| id   | select_type | table   | type  | possible_keys               | key                         | key_len | ref                      | rows | Extra                                           |
+------+-------------+---------+-------+-----------------------------+-----------------------------+---------+--------------------------+------+-------------------------------------------------+
|    1 | SIMPLE      | student | index | NULL                        | PRIMARY                     | 12      | NULL                     |    1 | Using index                                     |
|    1 | SIMPLE      | user    | ALL   | PRIMARY                     | NULL                        | NULL    | NULL                     |    1 | Using where; Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | parents | ref   | fk_responsavel_usuario1_idx | fk_responsavel_usuario1_idx | 4       | testeboletim.user.iduser |    1 | Using where; Using index                        |
+------+-------------+---------+-------+-----------------------------+-----------------------------+---------+--------------------------+------+-------------------------------------------------+
3 rows in set (0.00 sec)