帮我解决这个 MySql 完整外连接（或联合）

Question

这是将 MSSQL 转换为 MySQL 的结果。 以下是我试图开始工作的代码：

CREATE TEMPORARY TABLE PageIndex (
  IndexId int AUTO_INCREMENT NOT NULL PRIMARY KEY,
  ItemId VARCHAR(64)
);

INSERT INTO PageIndex (ItemId)
SELECT Paths.PathId
  FROM Paths,
       ((SELECT Paths.PathId
           FROM AllUsers, Paths
          WHERE Paths.ApplicationId = @ApplicationId
            AND AllUsers.PathId = Paths.PathId
            AND (@Path IS NULL
                OR Paths.LoweredPath LIKE LOWER(@Path))) AS SharedDataPerPath
          UNION -- This used to be a FULL OUTER JOIN but MySQL doesnt support that.
        (SELECT DISTINCT Paths.PathId
           FROM PerUser, Paths
          WHERE Paths.ApplicationId = @ApplicationId
            AND PerUser.PathId = Paths.PathId
            AND (@Path IS NULL
                OR Paths.LoweredPath LIKE LOWER(@Path))) AS UserDataPerPath
             ON SharedDataPerPath.PathId = UserDataPerPath.PathId)
          WHERE Paths.PathId = SharedDataPerPath.PathId OR Paths.PathId = UserDataPerPath.PathId
          ORDER BY Paths.Path ASC;

假设任何变量已经存在。 这个问题发生在“As SharedDataPerPath”部分，所以我猜测我给一个 select 语句添加了别名，以便您可以像 MySQL 中不支持的表一样访问它？ 如果表模式有帮助，请回复评论，我会将其添加到问题中。

提前致谢！

Answer 1

FULL OUTER JOIN 通常可以使用 LEFT JOIN 和 RIGHT JOIN 的 UNION 来模拟。 即它全部位于左侧和右侧，尽可能匹配连接标准。 根据我的经验，它通常很少被使用。 我有一个大型系统，它只使用一次。

由于 FULL OUTER JOIN 不可用，您似乎想要在这里做的是 UNION 两个集合并在两个集合之间设置一些 JOIN 标准子集，这确实是不可能的。 示例中 UNION 的两个集合不能有别名，也不能有尝试链接它们的 WHERE 子句。

Answer 2

-- Assuming these are defined in your store procedure
DECLARE @ApplicationId VARCHAR(64);
DECLARE @Path VARCHAR(256);
SET @ApplicationId = NULL;
Set @Path = NULL;

CREATE TEMPORARY TABLE SharedDataPerPath
(
  PathId VARCHAR(256)
);

CREATE TABLE UserDataPerPath
(
  PathId VARCHAR(256)
);

-- Do this instead of aliasing a select statment 'AS SharedDataPerPath'
INSERT INTO SharedDataPerPath
SELECT Paths.PathId
  FROM aspnet_PersonalizationAllUsers AllUsers, aspnet_Paths Paths
 WHERE Paths.ApplicationId = @ApplicationId
   AND AllUsers.PathId = Paths.PathId
   AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path));

-- Do this instead of aliasing a select statement 'AS UserDataPerPath'
INSERT INTO UserDataPerPath
SELECT DISTINCT Paths.PathId
  FROM aspnet_PersonalizationPerUser PerUser, aspnet_Paths Paths
 WHERE Paths.ApplicationId = @ApplicationId
   AND PerUser.PathId = Paths.PathId
   AND (@Path IS NULL OR Paths.LoweredPath LIKE LOWER(@Path));

-- This is how I would do my 'FULL OUTER JOIN'
SELECT Paths.PathId
    FROM `wppi_net_db`.`aspnet_Paths` Paths,
         (SELECT *
            FROM SharedDataPerPath AS s
            LEFT OUTER JOIN UserDataPerPath AS u
              ON s.PathID = u.PathID
           UNION -- OR UNION ALL see: http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/
          SELECT *
            FROM SharedDataPerPath AS s
           RIGHT OUTER JOIN UserDataPerPath AS u
              ON s.PathID = u.PathID) AS DataPerPaths
   WHERE Paths.PathId = DataPerPaths.PathId
   ORDER BY Paths.Path ASC;

-- At some point you need to drop your temp tables
DROP TEMPORARY TABLE SharedDataPerPath;
DROP TEMPORARY TABLE UserDataPerPath;

Answer 3

上面描述的将左外连接和右外连接联合在一起的方法效果很好，并且似乎是普遍接受的解决方案。 然而，正如我在阅读留言板上的各种示例时发现的那样，其中遗漏了一些细节。

1. 将用于连接的列上的表源从一个选择交换到另一个选择，以考虑外连接生成的 NULL。

   将

2. 将 COALESCE 函数添加到您的“固定列”，该函数也可能作为外连接生成的 NULL 返回。

   将 COALESCE 函数添加到您的“固定

例子：

SELECT
`Wins_VW`.`Year`,
`Wins_VW`.`Period`,
COALESCE(`Wins_VW`.`Wins`,0) as Wins,
COALESCE(`Leads_VW`.`Leads`,0) as Leads
FROM `Wins_VW` LEFT OUTER JOIN `Leads_VW`
ON( `Wins_VW`.`Year` = `Leads_VW`.`Year`
AND `Wins_VW`.`Period` = `Leads_VW`.`Period`)

UNION

SELECT
`Leads_VW`.`Year`,
`Leads_VW`.`Period`,
COALESCE(`Wins_VW`.`Wins`,0) as Wins,
COALESCE(`Leads_VW`.`Leads`,0) as Leads
FROM `Wins_VW` RIGHT OUTER JOIN `Leads_VW`
ON( `Wins_VW`.`Year` = `Leads_VW`.`Year`
AND `Wins_VW`.`Period` = `Leads_VW`.`Period`)