MySQL 完全加入?

2021-11-20 00:00:00 mysql

以下是我的查询，我想要如下所示的结果.我怎样才能在 mysql 中做到这一点?

Following are my query and I want the result as given below. How can I do this in mysql ? 

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

结果集需要看起来像这样:

The result-set need to look like this:

LastName      FirstName     OrderNo
Hansen        Ola          22456
Hansen        Ola          24562
Pettersen     Kari         77895
Pettersen     Kari         44678
Svendson      Tove   
                           34764

推荐答案

MySQL 缺乏对 FULL OUTER JOIN 的支持.

MySQL lacks support for FULL OUTER JOIN.

因此，如果您想在 MySQL 上模拟完整连接，请查看 这里 .

So if you want to emulate a Full join on MySQL take a look here .

通常建议的解决方法如下:

A commonly suggested workaround looks like this:

SELECT  t_13.value AS val13, t_17.value AS val17
FROM    t_13
LEFT JOIN
        t_17
ON      t_13.value = t_17.value
UNION ALL
SELECT  t_13.value AS val13, t_17.value AS val17
FROM    t_13
RIGHT JOIN
        t_17
ON      t_13.value = t_17.value
WHERE   t_13.value IS NULL
ORDER BY
        COALESCE(val13, val17)
LIMIT 30

相关文章