如何使用 IN 运算符整理 SQL_Latin1_General_CP1_CI_AS [英] How to collate SQL_Latin1_General_CP1_CI_AS using IN operator

问题描述

我想过滤电子邮件"上的记录，我的查询是这样的.

I want to filter records on 'Email' my query is like this.

SELECT * FROM #temp WHERE email NOT IN (SELECT email FROM Customer)

它给了我以下错误

无法解决排序规则冲突SQL_Latin1_General_CP1_CI_AS"之间和Latin1_General_CI_AS"等于操作.

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

如果有等号运算符 (=) 而不是 IN，我可以使用 collat​​e.但是在这里使用 Collat​​e 会给我带来语法错误.

I can use collate if there is equal operator (=) instead of IN. But using Collate here gives me syntax error.

SELECT * FROM #temp WHERE email NOT IN(SELECT email FROM Customer) 整理SQL_Latin1_General_CP1_CI_AS

SELECT * FROM #temp WHERE email NOT IN (SELECT email FROM Customer) collate SQL_Latin1_General_CP1_CI_AS

我该如何解决这个问题.我不能删除和重新创建表，因为它是实时数据库.

How can I resolve this. I can't drop and re create tables because it is live db.

#temp 表中的数据来自 SQL Server 2000，而 Customer 表中的数据来自 SQL Server 2005

Data in #temp table is from SQL Server 2000 and Customer table is in SQL Server 2005

谢谢.

推荐答案

...
NOT IN (SELECT email COLLATE SQL_Latin1_General_CP1_CI_AS AS email FROM Customer)

整理位基本上跟在列名后面.

The collate bit follows the column name, basically.

或者这个(这取决于你想要哪个):

Or this (it depends on which one you want):

SELECT * FROM #temp WHERE email collate SQL_Latin1_General_CP1_CI_AS NOT IN (SELECT email FROM Customer) 

最后，如果您的数据库是 SQL_Latin1_General_CP1_CI_AS，一种选择是使用 ...COLLATE Database_Default... 强制使用默认设置.

Finally, if your DB is SQL_Latin1_General_CP1_CI_AS, one option is to use ...COLLATE Database_Default... to coerce to a default setting.

这篇关于如何使用 IN 运算符整理 SQL_Latin1_General_CP1_CI_AS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！