sql - 如何在多个数据库中使用 EXCEPT 子句

Question

我有一个带有 except 子句的查询。除此之外，我的第二个查询来自与第一个查询使用的数据库不同的数据库。

看起来像这样

SELECT field1 as a
FROM table 1
EXCEPT
USE differentdb
SELECT field2 as a
FROM table 2

这个我也试过

SELECT field1 as a
FROM table 1
EXCEPT
USE differentdb
SELECT field2 as a
FROM differentdb.dbo.table2

我意识到 SQL 中不允许这样做，因为我收到以下错误:

Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

我想知道是否有另一种方法来编写此查询并完成跨数据库 EXCEPT 子句。

Answer 1

动态更改列的排序规则，如下所示:

SELECT field1 COLLATE SQL_Latin1_General_CP1_CI_AS as a
FROM table 1
EXCEPT
SELECT differentdb.SchemaName.field2 as a
FROM table 2