mysql - 从2 Union SQL语句获取不同的记录

我想征求一些sql专家的意见。
我编写了sql语句，用于从staff和alumnium表中获取作者的姓名。两个表中都会有作者的名字。所以逻辑是，如果作者名在staff中，则使用该名称查找校友表。
这是我的sql语句，看起来不错，但是它显示了staff和alumnium表中的相同作者名。

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

最佳答案

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

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
);

关于mysql - 从2 Union SQL语句获取不同的记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47985704/