mysql - 从 2 联合 sql 语句中获取不同的记录

Question

想请教一些sql高手。

我编写了从 staff 和 alumni 表中获取作者姓名的 sql 语句。一些作者的名字将出现在两个表中。所以逻辑是，如果作者姓名在 staff 中，则使用它，否则查找 alumni 表。

这是我的 sql 语句，看起来不错，但它在 staff 和 alumni 表中显示了相同的作者姓名。

SELECT DISTINCT AP.Author_name, P.people_id, P.Name, P.Journal_name, AP.Author_sortorder FROM  `Paper_Author` AS AP LEFT JOIN  `People` AS P ON ( AP.Author_id = P.people_id )  WHERE AP.Paper_id =3838

UNION  

SELECT DISTINCT AN.Author_name, N.People_id, N.Name, N.Journal_name, AN.Author_sortorder FROM  `Paper_Author` AS AN LEFT JOIN  `Alumni` AS N ON ( AN.Author_id = N.People_id )  WHERE AN.Paper_id =3838 ORDER BY Author_sortorder LIMIT 0 , 30

结果:

people_id--  Author_name--  Journal_name--  
    1        Name1              A1          
    2        Name2              B1          
    3        Name3              C1
    3        Name3              C1
    4        Name4              D
    4        Name4

预期结果:

 people_id--  Author_name--  Journal_name--  
    1           Name1            A1          
    2           Name2            B1          
    3           Name3            C1
    4           Name4            D 

Answer 1

这可能可以通过使用原始结果作为子查询的附加选择来解决

SELECT DISTINCT * FROM (
    SELECT DISTINCT AP.Author_name, P.people_id, P.Name, P.Journal_name, AP.Author_sortorder FROM  `Paper_Author` AS AP LEFT JOIN  `People` AS P ON ( AP.Author_id = P.people_id )  WHERE AP.Paper_id =3838

    UNION  

    SELECT DISTINCT AN.Author_name, N.People_id, N.Name, N.Journal_name, AN.Author_sortorder FROM  `Paper_Author` AS AN LEFT JOIN  `Alumni` AS N ON ( AN.Author_id = N.People_id )  WHERE AN.Paper_id =3838 ORDER BY Author_sortorder LIMIT 0 , 30
);