主页 / dba / 问题 / 127970
Accepted
Bjørn Fridal
Asked: 2016-02-02 14:57:00 +0800 CST

如何将 UNION 与 EXISTS 结合起来？

  • 772

我有一个看起来像这样的查询：

SELECT PubKey, Title FROM Publication
UNION
SELECT NoteKey, Title, FROM Note

哪个工作正常。当我尝试向其中添加 EXIST 时，我的麻烦就开始了：

SELECT PubKey, Title FROM Publication
UNION
SELECT NoteKey, Title, FROM Note
WHERE EXISTS (SELECT * FROM UserPublication WHERE UserPublication.PubKey = Publication.PubKey)

这会引发此错误：无法绑定多部分标识符“PubKey”。

能不能换一种写法？

union exists

2 个回答

  1. Best Answer

    该WHERE子句特定于每个人SELECT，因此您需要将您拥有的那个移动到第一个，SELECT如下所示：

    SELECT PubKey, Title FROM Publication
WHERE EXISTS (
    SELECT * 
      FROM UserPublication 
     WHERE UserPublication.PubKey = Publication.PubKey
)
UNION
SELECT NoteKey, Title FROM Note

    但是，如果要WHERE在整个结果集中指定子句。必须这样做：

    SELECT * FROM (
    SELECT PubKey, Title FROM Publication
    UNION
    SELECT NoteKey, Title FROM Note
) AS A
WHERE EXISTS (
    SELECT * 
      FROM UserPublication 
     WHERE UserPublication.PubKey = A.PubKey
)
    • 4 
  2. /** Reference to the following script **/
DECLARE @chk NVARCHAR(1)='B';
/** change @chk as 'C' to see the result**/
WITH T1 AS(SELECT * FROM(VALUES('A',1))a(F1,F2)
),T2 AS(SELECT * FROM(VALUES('B',2))a(F1,F2)
),T3 AS(SELECT * FROM(VALUES('C',3))a(F1,F2)
),CHK AS(SELECT CASE WHEN EXISTS(SELECT * FROM T2 WHERE F1=@chk) THEN 'B' ELSE 'C' END[chk_f]
) SELECT * FROM T1 UNION SELECT T2.* FROM T2,CHK WHERE chk_f='B' UNION SELECT T3.* FROM T3,CHK WHERE chk_f='C'
    • 0

相关问题