mysql - 连接2个涉及同一个表的sql查询，我需要取回id

Question

我有这 2 个疑问

select 
    count(1),
    female.first_name,
    female.last_name,
    male.first_name,
    male.last_name
from
    new_person as male
        left join
    new_person as female ON male.spouse_id = female.id
where
    male.spouse_id <> '0'
group by female.first_name , female.last_name , male.first_name , male.last_name;

第二个 1 看起来像这样

    select 
    male.first_name as fnf,
    male.last_name as lnf,
    female.first_name as fnm,
    female.last_name as lnm
from
    new_person as p1
        left join
    new_person as male ON p1.father_id = male.id
        left join
    new_person as female ON p1.mother_id = female.id
where
    p1.last_name <> ''
        and p1.last_name <> 'N.N.'
        and ((male.last_name <> ''
        and male.last_name <> 'N.N.')
        or (female.last_name <> ''
        and female.last_name <> 'N.N.'))

现在我想把这些结合起来喜欢其中maleq1.name =maleq2.name并且femaleq1.name =femaleq2然后将 q1 和 q2 中雄性和雌性的 id 告诉我

我的名字是指名字和姓氏

Answer 1

这是我的建议:从每个查询中创建一个 View 。然后使用您的两个 View 编写第三个查询。

但是，您的第一个查询是多个记录的聚合/汇总/任何内容。使用 id 添加新字段到查询

CREATE VIEW q1 AS
select 
    count(1),
    female.first_name,
    female.last_name,
    male.first_name,
    male.last_name,
    GROUP_CONCAT(male.id),
    GROUP_CONCAT(female.id)
from
    new_person as male
        left join
    new_person as female ON male.spouse_id = female.id
where
    male.spouse_id <> '0'
group by female.first_name , female.last_name , male.first_name , male.last_name;

然后是你的第二个查询

CREATE VIEW q2 AS
select 
    male.first_name as fnf,
    male.last_name as lnf,
    female.first_name as fnm,
    female.last_name as lnm,
    male.id AS male_id,
   female.id as female_id
from
    new_person as p1
        left join
    new_person as male ON p1.father_id = male.id
        left join
    new_person as female ON p1.mother_id = female.id
where
    p1.last_name <> ''
        and p1.last_name <> 'N.N.'
        and ((male.last_name <> ''
        and male.last_name <> 'N.N.')
        or (female.last_name <> ''
        and female.last_name <> 'N.N.'))

将它们用于第三个查询