通过此MySql完整外部联接(或联合)帮助我 [英] Help me out with this MySql full outer join (or union)

问题描述

这来自将MSSQL转换为MySql.以下是我要开始使用的代码:

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

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中的表那样可以访问它?如果表模式有帮助，请回复并添加评论，然后将其添加到问题中.

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.

提前谢谢！

推荐答案

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

这篇关于通过此MySql完整外部联接(或联合)帮助我的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！