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

This is coming from converting MSSQL to MySql. The following is code I'm trying to get to work:

这是将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;

Assume any variables exist already. Where this is breaking is on the 'As SharedDataPerPath' part, so I'm guessing that I aliasing a select statement so that you can access it like a table isn't supported in MySQL? If table schema would help, reply with a comment and I will add that to the question.

假设已存在任何变量。这是打破'As SharedDataPerPath'部分,所以我猜我别名一个select语句,以便你可以访问它像MySQL不支持表?如果表模式有帮助,请回复评论,我会将其添加到问题中。

Thanks in advance!

提前致谢!

3 个解决方案

#1

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

#2

A FULL OUTER JOIN can often be simulated with the UNION of both LEFT JOIN and RIGHT JOIN. i.e. it is all on the left and those on the right, matching where possible on the join criteria. It is usually extremely rarely used, in my experience. I have a large system where it is only used once.

通常可以使用LEFT JOIN和RIGHT JOIN的UNION模拟FULL OUTER JOIN。即它全部在左侧,而在右侧,在可能的情况下匹配连接标准。根据我的经验,它通常极少使用。我有一个大型系统,只使用一次。

What you seem to be wanting to do here because FULL OUTER JOIN is not available is to UNION two sets and set some JOIN criteria between the two subsets, which really isn't possible. The two sets that are UNIONed in your example cannot have aliases, nor can they have a WHERE clause which attempts to link them.

你似乎想在这里做什么,因为FULL OUTER JOIN不可用是UNION两个集合并在两个子集之间设置一些JOIN标准,这实际上是不可能的。在您的示例中,UNIONed的两个集合不能具有别名,也不能具有尝试链接它们的WHERE子句。

#3

The method described above with the left and right outer joins unioned together works well and seems to be the commonly accepted solution. There are some details left out of this however as I have found when reading various examples across the message boards.

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

1. Swap your the table sources on the columns you use to join with from one select to the other to account for NULLs produced by the outer joins.

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

2. Add COALESCE functions to your "fixed columns" that might also come back as NULLs produced by the outer joins.

   将COALESCE函数添加到“固定列”,这些列也可能作为外连接生成的NULL返回。

Example:

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