mysql - 需要帮助制定 SQL 查询 (mysql)

Question

就目前情况而言，这个问题不太适合我们的问答形式。我们希望答案得到事实、引用资料或专业知识的支持，但这个问题可能会引发辩论、争论、民意调查或扩展讨论。如果您觉得这个问题可以改进并可能重新开放， visit the help center以获得指导。

已关闭 10 年前。

我有这个表结构:

CREATE TABLE IF NOT EXISTS `person` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `father` text NOT NULL,
  `mother` text NOT NULL,
  `name` text NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

和数据:

(`ID`, `father`, `mother`, `name`)
( 1       ''        ''     'Robert Darwin'),
( 2       ''        ''     'Josiah Wedgwood'),
( 3       ''        ''     'Sarah Wedgwood'),
( 4       ''        ''     'Mary Howard'),
( 5       1         ''     'Erasmus Darwin'),
( 6       ''        ''     'Elizabeth Allen'),
( 7       2         3      'Josiah II'),
( 8       2         3      'Susannah Wedgwood'),
( 9       5         4      'Robert Waring Darwin'),
( 10      7         6      'Josiah III'),
( 11      7         6      'Emma Wedgwood'),
( 12      9         8      'Charles Robert'),
( 13      9         8      'Caroline Sarah Darwin'),
( 14      10        13     'Margaret Wedgwood'),
( 15      11        12     'William Erasmus');

我需要收集配偶也是他们的一级表亲的人员名单？

任何人都可以帮助我如何制定 MySQL 查询吗？

Answer 1

假设有 child 的人已婚(没有 child 的人未婚)，那么可能可以这样做。但可能效率不高。

SELECT DISTINCT f.name
FROM (
SELECT a.ID AS Parent, c.ID AS GrandChild
FROM person a
INNER JOIN person b ON a.ID = b.father
INNER JOIN person c ON b.ID = c.father
UNION
SELECT  a.ID, c.ID
FROM person a
INNER JOIN person b ON a.ID = b.father
INNER JOIN person c ON b.ID = c.mother
UNION
SELECT  a.ID, c.ID
FROM person a
INNER JOIN person b ON a.ID = b.mother
INNER JOIN person c ON b.ID = c.mother
UNION
SELECT  a.ID, c.ID
FROM person a
INNER JOIN person b ON a.ID = b.mother
INNER JOIN person c ON b.ID = c.father) y
INNER JOIN (
SELECT a.ID AS Parent, c.ID AS GrandChild
FROM person a
INNER JOIN person b ON a.ID = b.father
INNER JOIN person c ON b.ID = c.father
UNION
SELECT  a.ID, c.ID
FROM person a
INNER JOIN person b ON a.ID = b.father
INNER JOIN person c ON b.ID = c.mother
UNION
SELECT  a.ID, c.ID
FROM person a
INNER JOIN person b ON a.ID = b.mother
INNER JOIN person c ON b.ID = c.mother
UNION
SELECT  a.ID, c.ID
FROM person a
INNER JOIN person b ON a.ID = b.mother
INNER JOIN person c ON b.ID = c.father) x
ON y.Parent = x.Parent
INNER JOIN person z ON z.father = x.GrandChild AND z.mother = y.GrandChild
INNER JOIN person f ON f.ID = z.father OR f.ID = z.mother

有 2 个大的子选择，每个子选择都有一个人和他们的孙子，在 ID 字段上连接在一起，以便他们共享祖 parent 。然后将它们与 person 表连接起来，将一个子选择视为父亲，将一个子选择视为母亲，以获得 parent 共享祖 parent 的任何人，然后再次与 person 表连接以获得实际的 parent 。

编辑 - 较短的代码。

我已经减少了上述内容，因此通过在 JOIN 的 ON 子句中使用 OR 来避免使用 UNIONS。我倾向于讨厌在 JOIN 中使用 OR，但它确实使其更具可读性。

SELECT DISTINCT f.name
FROM (
SELECT a.ID AS Parent, c.ID AS GrandChild
FROM person a
INNER JOIN person b ON a.ID = b.father OR a.ID = b.mother
INNER JOIN person c ON b.ID = c.father OR b.ID = c.mother
) y
INNER JOIN (
SELECT a.ID AS Parent, c.ID AS GrandChild
FROM person a
INNER JOIN person b ON a.ID = b.father OR a.ID = b.mother
INNER JOIN person c ON b.ID = c.father OR b.ID = c.mother
) x
ON y.Parent = x.Parent
INNER JOIN person z ON z.father = x.GrandChild AND z.mother = y.GrandChild
INNER JOIN person f ON f.ID = z.father OR f.ID = z.mother