UNION SQL语句不起作用

问题描述：

为什么这是错的，我该如何正确使用它？UNION SQL语句不起作用

SELECT  PublicationID 
FROM   (SELECT DISTINCT pat.PublicationID 
         FROM   dbo.PubAdvTransData AS pat INNER JOIN 
              dbo.PubAdvertiser AS pa ON pat.AdvTransID = pa.AdvTransID 
         WHERE  (pat.LastAdDate > '7/1/2009') AND (pat.PublicationID = 29171)) AS table1 
UNION 
SELECT  PublicationAreaBuy.AreaBuyID AS PublicationID 
FROM   PublicationAreaBuy INNER JOIN 
         table1 AS table1_1 ON table1.publicationID =  PublicationAreaBuy.PublicationID

错误在于table1是无效的对象。

谢谢。

答

嗯..是的。

即使结果通过联合结合在一起，两个select语句也是彼此不同的。

您使用的SQL服务器包括哪些版本？

答

您有一个范围界定问题。工会组合了两个单独的查询。因此，如果您将查询分开：

SELECT PublicationID 
FROM (SELECT DISTINCT pat.PublicationID 
     FROM dbo.PubAdvTransData AS pat 
     INNER JOIN dbo.PubAdvertiser AS pa 
     ON pat.AdvTransID = pa.AdvTransID 
     WHERE (pat.LastAdDate > '7/1/2009') AND (pat.PublicationID = 29171) 
    ) AS table1 



SELECT PublicationAreaBuy.AreaBuyID AS PublicationID 
FROM PublicationAreaBuy 
INNER JOIN table1 AS table1_1 
    ON table1.publicationID = PublicationAreaBuy.PublicationID

您可以看到在第二个查询中没有像table1那样的东西。还有一些其他的方式来完成你想要做什么：

重复子查询（丑陋）
使用公用表表达式（只有当这是T-SQL）
使用#temporary @table（同样，我只知道如何在T-SQL中执行此操作）

如果我们要复制子查询，它看起来像这样。但是，顶部查询没有条件，所以它不需要是子查询：

SELECT DISTINCT pat.PublicationID 
FROM dbo.PubAdvTransData AS pat 
INNER JOIN dbo.PubAdvertiser AS pa 
    ON pat.AdvTransID = pa.AdvTransID 
WHERE (pat.LastAdDate > '7/1/2009') AND (pat.PublicationID = 29171) 


UNION 

SELECT PublicationAreaBuy.AreaBuyID AS PublicationID 
FROM PublicationAreaBuy 
INNER JOIN (SELECT DISTINCT pat.PublicationID 
     FROM dbo.PubAdvTransData AS pat 
     INNER JOIN dbo.PubAdvertiser AS pa 
     ON pat.AdvTransID = pa.AdvTransID 
     WHERE (pat.LastAdDate > '7/1/2009') AND (pat.PublicationID = 29171) 
    ) AS table1 
    ON table1.publicationID = PublicationAreaBuy.PublicationID

我必须得到'丑陋'的答案，我会如何写这个？ – flavour404 2009-07-08 21:17:22

答

AFAIK，您不能引用另一个查询。你们结合在一起的两个查询必须是分开的，不同的。

当你考虑这个问题时，它是有道理的，因为联盟真正所做的就是将两个查询的输出连续拼接在一起。

答

尝试使用，而不是你的第一个查询CTE

WITH MyCTE AS 
(
    SELECT DISTINCT pat.PublicationID 
    FROM dbo.PubAdvTransData AS pat 
     INNER JOIN dbo.PubAdvertiser AS pa ON pat.AdvTransID = pa.AdvTransID 
    WHERE (pat.LastAdDate > '7/1/2009') AND (pat.PublicationID = 29171) 
) 
SELECT PublicationAreaBuy.AreaBuyID AS PublicationID 
FROM PublicationAreaBuy 
    INNER JOIN MyCTE ON MyCTE.publicationID = PublicationAreaBuy.PublicationID

答

你可以使用一个临时表，但我觉得有可能是完全有更好的方式。不知道你的数据库模式，或者你想要达到的目标，很难推荐其他任何东西。

CREATE TABLE #temp 
(
    PublicationID int 
) 

SELECT DISTINCT INTO #temp pat.PublicationID 
        FROM dbo.PubAdvTransData AS pat 
        INNER JOIN dbo.PubAdvertiser AS pa ON pat.AdvTransID = pa.AdvTransID 
        WHERE (pat.LastAdDate > '7/1/2009') AND (pat.PublicationID = 29171) 

    SELECT PublicationID 
    FROM #temp 
    UNION 
    SELECT PublicationAreaBuy.AreaBuyID AS PublicationID 
    FROM PublicationAreaBuy 
    INNER JOIN #temp As Table1 ON Table1.PublicationID = PublicationAreaBuy.PublicationID

免责声明：我手工输入了这个信息，以便在运行前检查语法错误。

UNION SQL语句不起作用

相关推荐