在Microsoft SQL server中的两个伪(派生)表之间相交

Question

我试图找出是否有一个列的两个伪表之间的任何差异(看看是否每个元素在一个表是在另一个)

代码:

SELECT * 
FROM
(SELECT Orders.OrderID
FROM (((Categories FULL OUTER JOIN Products on Categories.CategoryID = Products.CategoryID)
FULL OUTER JOIN  OrderDetails on OrderDetails.ProductID = Products.ProductID)
FULL OUTER JOIN  Orders on Orders.OrderID = OrderDetails.OrderID)
FULL OUTER JOIN  Suppliers on Suppliers.SupplierID = Products.SupplierID
WHERE CategoryName LIKE 'Dairy%' AND country ='France')
INTERSECT
(SELECT Orders.OrderID
FROM (((Categories LEFT JOIN Products on Categories.CategoryID = Products.CategoryID)
LEFT JOIN OrderDetails on OrderDetails.ProductID = Products.ProductID)
LEFT JOIN Orders on Orders.OrderID = OrderDetails.OrderID)
LEFT JOIN Suppliers on Suppliers.SupplierID = Products.SupplierID
WHERE CategoryName LIKE 'Dairy%' AND country ='France')

输出示例:

查询的顶部给出了前三个结果:

OrderID
10255
10267
10275

代码的第二部分(在INTERSECT下面)给出:

OrderID
10255
10267
10275

现在由于某种原因，代码没有在交叉路口运行。代码单独工作，为INSERSECT上面和下面的子查询提供单独的列，但整体上它不起作用。我得到的错误信息是:

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'INTERSECT'.
Completion time: 2022-05-23T14:10:06.3560367+01:00

你知道怎么解决这个问题吗?如果有用的话，机密数据库用红色下划线。

谢谢。

Answer 1

只需删除FROM和外括号，如下所示:

SELECT Orders.OrderID
FROM (((Categories FULL OUTER JOIN Products on Categories.CategoryID = Products.CategoryID)
FULL OUTER JOIN  OrderDetails on OrderDetails.ProductID = Products.ProductID)
FULL OUTER JOIN  Orders on Orders.OrderID = OrderDetails.OrderID)
FULL OUTER JOIN  Suppliers on Suppliers.SupplierID = Products.SupplierID
WHERE CategoryName LIKE 'Dairy%' AND country ='France'
INTERSECT
SELECT Orders.OrderID
FROM (((Categories LEFT JOIN Products on Categories.CategoryID = Products.CategoryID)
LEFT JOIN OrderDetails on OrderDetails.ProductID = Products.ProductID)
LEFT JOIN Orders on Orders.OrderID = OrderDetails.OrderID)
LEFT JOIN Suppliers on Suppliers.SupplierID = Products.SupplierID
WHERE CategoryName LIKE 'Dairy%' AND country ='France'

Answer 2

问题是，你有一个FROM子句试图运行2个子选择括在括号内。

SELECT 
*
FROM
(SELECT 1 AS a)

产生错误:

留言102,15层，状态1，线路4')'附近语法错误。

不过，添加别名也可以:

SELECT 
*
FROM
(SELECT 1 AS a) as a

更新:

您只需要为第一个查询指定别名:

SELECT 
*
FROM
(SELECT 1 AS a) as a
INTERSECT
(SELECT 1 AS a)

或者，按照RBarryYoung的建议，完全去掉括号。

Answer 3

只是在INTERSECT工作前放一个x。例如:

SELECT * 
FROM
(SELECT Orders.OrderID
FROM (((Categories FULL OUTER JOIN Products on Categories.CategoryID = Products.CategoryID)
FULL OUTER JOIN  OrderDetails on OrderDetails.ProductID = Products.ProductID)
FULL OUTER JOIN  Orders on Orders.OrderID = OrderDetails.OrderID)
FULL OUTER JOIN  Suppliers on Suppliers.SupplierID = Products.SupplierID
WHERE CategoryName LIKE 'Dairy%' AND country ='France') x
INTERSECT
(SELECT Orders.OrderID
FROM (((Categories LEFT JOIN Products on Categories.CategoryID = Products.CategoryID)
LEFT JOIN OrderDetails on OrderDetails.ProductID = Products.ProductID)
LEFT JOIN Orders on Orders.OrderID = OrderDetails.OrderID)
LEFT JOIN Suppliers on Suppliers.SupplierID = Products.SupplierID
WHERE CategoryName LIKE 'Dairy%' AND country ='France')