与临时表中的列进行比较时 SQL 排序规则冲突
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1404880/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Collation conflict when comparing to a column in a temp table
提问by Justin
I have a SQL query that compares a value in the database to a constant:
我有一个 SQL 查询，它将数据库中的值与常量进行比较：
SELECT * FROM my_table
INNER JOIN #TempTable tem
ON my_table.id = temp.id
AND my_table.key = 'SOME STRING'
And I get the error:
我得到错误：
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
How can I get around this? (without making changes to the database)
我怎样才能解决这个问题？（不更改数据库）
UPDATE: I get this error even if I remove the last like (the string comparison)...
更新：即使我删除了最后一个像（字符串比较），我也会收到此错误...
回答by Quassnoi
Seems your id's are VARCHARs with different collations.
似乎您id的VARCHARis 具有不同的排序规则。
Try this:
尝试这个：
SELECT *
FROM my_table
INNER JOIN
#TempTable tem
ON my_table.id = temp.id COLLATE SQL_Latin1_General_CP1_CI_AS
AND my_table.key = 'SOME STRING'
回答by Dimi Takis
Specify the collation inside the declaration of your temp table.
在临时表的声明中指定排序规则。
CREATE TABLE #TempTable (ID NVARCHAR(255) COLLATE database_default)
回答by Daniel Elliott
The problem is the temp table. It uses the collation of the tempdb.
问题是临时表。它使用 tempdb 的排序规则。
You could create a table in your actual db and not a temp table and then they would have the same collation. Or specify collation upon creating temp table.
您可以在实际数据库中创建一个表而不是临时表，然后它们将具有相同的排序规则。或者在创建临时表时指定排序规则。
回答by Kev Riley
try
尝试
SELECT * FROM my_table
INNER JOIN #TempTable temp
ON my_table.id = temp.id collate database_default
AND my_table.key = 'SOME STRING'
