需要帮助来制定SQL查询（mysql）

I have this table structure:

我有这个表结构:

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 ;

and the data:

和数据:

(`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');

What I need is to gather the list of persons whose SPOUSE is also their FIRST-DEGREE COUSIN?

我需要的是收集SPOUSE也是他们的FIRST-DEGREE COUSIN的人员名单?

Can anyone help me with how to formulate the MySQL query?

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

2 个解决方案

#1

Even though this question sounds so much like a homework, but I'm not someone who judges. I'm doing this as an exercise to my mind:

即使这个问题听起来像家庭作业,但我不是一个评判的人。我这样做是为了锻炼我的想法:

SELECT p1.father, p1.mother
FROM person p1
WHERE
    (p1.father, p1.mother) IN (
        SELECT firstSiblingChild.ID AS ID1, secondSiblingChild.ID AS ID2
        FROM (
            SELECT p1.ID AS ID1, p2.ID AS ID2
            FROM person p1
            INNER JOIN person p2 ON (p1.father = p2.father AND p1.mother = p2.mother)
            WHERE
                p1.father <> ""
            AND
                p1.mother <> ""
            AND
                p1.ID <> p2.ID
        ) siblings
        INNER JOIN person firstSiblingChild ON (siblings.ID1 = firstSiblingChild.father OR siblings.ID1 = firstSiblingChild.mother)
        INNER JOIN person secondSiblingChild ON (siblings.ID2 = secondSiblingChild.father OR siblings.ID2 = secondSiblingChild.mother)
    )

#2

Assuming that people who have a child are married (and those with no child are not married) then possibly it can be done like this. But probably not that efficient.

假设有孩子的人已经结婚(没有孩子的人没有结婚),那么可能就这样做了。但可能效率不高。

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

There are 2 large sub selects which each get a person and their grand children, joined together on the ID fields so they share the grandparent. These are then joined against the persons table, treating one subselect as the fathers and one as the mothers, to get any people whose parents share a grand parent, then that is joined against the person table again to get the actual parents.

有两个大的子选择,每个选择一个人和他们的大孩子,在ID字段上连接在一起,所以他们共享祖父母。然后将这些与人员表联系在一起,将一个子选择作为父亲,一个作为母亲,以使任何父母与父母共享的人,然后再次与人员表联系以获得实际的父母。

EDIT - shorter code.

编辑 - 更短的代码。

I have reduced the above so avoid the UNIONS by using OR in the ON clauses of the JOINs. I tend to hate using an OR in a JOIN but it does make it more readable.

我已经减少了上述内容,因此在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

#1