SQL联合问题

Question

这是我的查询：

SELECT publicationId AS PublicationID
  FROM dbo.PublicationOwner
 WHERE ownerId = 31331
UNION
SELECT AreaBuy.AreaBuyID AS PublicationID
  FROM AreaBuy 
  JOIN PublicationAreaBuy ON AreaBuy.AreaBuyID = PublicationAreaBuy.AreaBuyID
 WHERE PublicationAreaBuy.PublicationID IN (SELECT publicationId
                                              FROM dbo.PublicationOwner
                                             WHERE ownerId = 31331)
ORDER BY publicationId

我想做的是：

获取所有者 id 等于 31331 的出版物 ID 列表，并添加（联合）任何区域购买 id（作为出版物 id）（如果区域购买表中的出版物 id）在第一个查询的结果中。

怎么了？是的，31331 是正确的所有者 ID，并且确实返回了也在区域购买表中的出版物 ID。

Answer 1

看起来像是经典的混搭。我没有看到属性 PublicationId 和 AreaBuyId 之间有任何相似之处。
可能它们只是命名非常糟糕的列:-)。将如此不同的属性结合起来似乎并不是正确的方法。

为什么不：

SELECT O.publicationId , A,AreaBuyId
  FROM dbo.PublicationOwner O
  LEFT OUTER JOIN dbo.AreaBuy A
   ON O.AreaBuyId = A.AreaBuyId
 WHERE OwnerId =31331 

得到你想要的东西？

Answer 2

我喜欢为此使用递归 CTE 的想法，因为我们想要添加到原始列表中。

DECLARE @ownerid INT;
SET @ownerid = 31331;

WITH Publications AS
(
SELECT publicationId AS PublicationID, 'P' AS Rowtype
FROM dbo.PublicationOwner 
WHERE ownerId = @ownerid

UNION ALL

--Note, I am not using the AreaBuy table, because the info is in PublicationAreaBuy
SELECT a.AreaBuyID, 'A' AS Rowtype
FROM Publications AS p
JOIN PublicationAreaBuy AS a
ON a.PublicationID = p.PublicationID
)
SELECT *
FROM Publications
ORDER BY PublicationID
;

并且...执行连接将阻止 IN 子句中 NULL 的任何问题。

编辑：下一个查询将在 SQL 2005 之前的版本中运行

DECLARE @ownerid INT
SET @ownerid = 31331

SELECT publicationId AS PublicationID, 'P' AS Rowtype
FROM dbo.PublicationOwner 
WHERE ownerId = @ownerid

UNION ALL

--Note, I am not using the AreaBuy table, because the info is in PublicationAreaBuy
SELECT a.AreaBuyID, 'A' AS Rowtype
FROM (
      SELECT publicationId AS PublicationID, 'P' AS Rowtype
      FROM dbo.PublicationOwner 
      WHERE ownerId = @ownerid
   ) AS p
JOIN PublicationAreaBuy AS a
ON a.PublicationID = p.PublicationID

ORDER BY PublicationID