在格式化 MySQL 查询以返回信息方面需要一些帮助

Question

这个问题是我的另一个问题的扩展@需要一些关于在MySQL中编码多：多关系的建议和反馈...

我有以下用于表的 MySQL 代码：

DROP TABLE IF EXISTS `person` ;
CREATE TABLE `person` (
  `personID` INT(5) NOT NULL AUTO_INCREMENT ,
  `firstName` VARCHAR(50) NOT NULL ,
  `lastName` VARCHAR(50) NOT NULL ,
  `dateOfBirth` DATE NOT NULL ,
  `personType` CHAR(6) NOT NULL, 
  `photo` BLOB NULL DEFAULT NULL ,
  PRIMARY KEY (`personID`))
ENGINE = InnoDB;
SHOW WARNINGS;
DROP TABLE IF EXISTS `parent` ;
CREATE TABLE `parent` (
  `parentID` INT(5) NOT NULL,
  PRIMARY KEY (`parentID`), 
  FOREIGN KEY (`parentID`) REFERENCES `person` (`personID`) 
  ON DELETE CASCADE 
  ON UPDATE CASCADE)
ENGINE = InnoDB;
SHOW WARNINGS;
DROP TABLE IF EXISTS `player` ;
CREATE TABLE Player (
    `playerID` INT(5) NOT NULL,
    `motherID` INT(5),
    `fatherID` INT(5),
    `schoolID` INT(5),
    PRIMARY KEY (`playerID`), 
    FOREIGN KEY (`playerID`) REFERENCES `person` (`personID`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE,
    FOREIGN KEY (`motherID`) REFERENCES `parent` (`parentID`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE,
    FOREIGN KEY (`fatherID`) REFERENCES `parent` (`parentID`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE, 
    FOREIGN KEY (`schoolID`) REFERENCES `school` (`schoolID`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE)
ENGINE = InnoDB;
SHOW WARNINGS;

我想格式化查询，使其返回如下信息：

+----------+-----------------+----------------+-----------------+----------------+
| ParentID | ParentFirstName | ParentLastName | PlayerFirstName | PlayerLastName |
+----------+-----------------+----------------+-----------------+----------------+
|        1 | John            | Doe            | Maggie          | Doe            |
|        1 | John            | Doe            | Rob             | Doe            |
|        2 | Jane            | Doe            | Rob             | Doe            |
|        2 | Jane            | Doe            | Maggie          | Doe            |
|        3 | Peter           | Smith          | Neil            | Smith          |
|        3 | Peter           | Smith          | Chad            | Smith          |
|        4 | Mary            | Mason          | Neil            | Smith          |
|        4 | Mary            | Mason          | Chad            | Smith          |
+----------+-----------------+----------------+-----------------+----------------+

我注意到在上面的可视化中，名称重复了几次，我也想知道使用GROUP_CONCAT是否以某种方式将它们合并在一起是个好主意。

我无法以这种方式连接这三个表以生成所需的查询。我在 http://sqlfiddle.com/#!2/baf8d 中找到了一些灵感（由这里的用户提供），但是我无法自定义它以满足我自己的需求（在示例中，名字和姓氏在每个父/玩家表中，而我从 Person 超类继承这些字段。

下面的代码是我决定在这里提问之前得到的最接近的代码。我想以某种方式将这些查询合并在一起以匹配上述结果，但我被困住了......

mysql> select person.firstName as ParentFirstName, person.lastName as ParentLastName from person where
    -> person.personID IN (select * from parent);
+-----------------+----------------+
| ParentFirstName | ParentLastName |
+-----------------+----------------+
| John            | Doe            |
| Jane            | Doe            |
| Peter           | Smith          |
| Mary            | Mason          |
+-----------------+----------------+
mysql> select person.firstName as ChildFirstName, person.lastName as ChildLastName from person where
    -> person.personID IN (select player.playerID from player);
+----------------+---------------+
| ChildFirstName | ChildLastName |
+----------------+---------------+
| Maggie         | Doe           |
| Rob            | Doe           |
| Neil           | Smith         |
| Chad           | Smith         |
+----------------+---------------+

我觉得在我的数据库模式中使用 Person 超类是必要的（因为玩家/父实体和其他"人"实体会有类似的详细信息），这是我宁愿留下的东西。如果您能够帮助我摆脱这个问题，我将不胜感激。

提前感谢！！

Answer 1

对于您显示的确切输出，您希望按如下方式联接表：

SELECT
        parent.parentID  AS ParentID,
  ParentPerson.firstName AS ParentFirstName,
  ParentPerson.lastName  AS  ParentLastName,
   ChildPerson.firstName AS PlayerFirstName,
   ChildPerson.lastName  AS  PlayerLastName
FROM
       parent
  JOIN Player ON (parent.parentID IN (Player.motherID, Player.fatherID))
  JOIN person AS ParentPerson ON (ParentPerson.personID = parent.parentID)
  JOIN person AS  ChildPerson ON ( ChildPerson.personID = Player.playerID)

Answer 2

请检查一下。

select 
(select P.FirstName  from Parent as P where P.ParentID = pl.FatherID ) as FatherName,
(select P.FirstName  from Parent as P where P.ParentID = pl.MotherID ) as MotherName,
pl.FirstName as PlayerFirstName, pl.LastName as PlayerLastName from player pl