SQL 无法解决 SELECT 语句中第 4 列的排序规则冲突
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5026553/
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
Cannot resolve collation conflict for column 4 in SELECT statement
提问by leen3o
I am trying to get some SQL to execute but am getting the following error
我正在尝试执行一些 SQL，但出现以下错误
Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict for column 4 in SELECT statement.
But cannot seem to figure out where the problem is??? Any help would be very much appreciated.
但似乎无法弄清楚问题出在哪里？？？任何帮助将不胜感激。
SELECT MEMBTYPEID.text AS MemberType, MEMBLST.nodeId, MEMBTYPES.Name AS MemberField,
ISNULL(CASE WHEN MEMBTYPES.datatypeID IN
(SELECT NodeId
FROM DBO.CMSDATATYPE
WHERE DBTYPE = 'Nvarchar') THEN MEMBDATA.[dataNvarchar] WHEN MEMBTYPES.datatypeID IN
(SELECT NodeId
FROM DBO.CMSDATATYPE
WHERE DBTYPE = 'Ntext') THEN MEMBDATA.[dataNtext] WHEN MEMBTYPES.datatypeID IN
(SELECT NodeId
FROM DBO.CMSDATATYPE
WHERE DBTYPE = 'Date') THEN CONVERT(NVARCHAR, MEMBDATA.[dataDate]) WHEN MEMBTYPES.datatypeID IN
(SELECT NodeId
FROM DBO.CMSDATATYPE
WHERE DBTYPE = 'Integer') THEN CASE WHEN
(SELECT value
FROM [dbo].[cmsDataTypePreValues]
WHERE datatypenodeid = MEMBTYPES.[dataTypeId] AND id = CONVERT(INT, MEMBDATA.[dataInt])) IS NOT NULL THEN
(SELECT value
FROM [dbo].[cmsDataTypePreValues]
WHERE datatypenodeid = MEMBTYPES.[dataTypeId] AND id = CONVERT(INT, MEMBDATA.[dataInt])) ELSE CONVERT(NVARCHAR,
MEMBDATA.[dataInt]) END ELSE NULL END, '') AS MemberData
FROM (SELECT id, text
FROM dbo.umbracoNode
WHERE (nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43')) AS MEMBTYPEID LEFT OUTER JOIN
(SELECT nodeId, contentType
FROM dbo.cmsContent) AS MEMBLST ON MEMBLST.contentType = MEMBTYPEID.id LEFT OUTER JOIN
dbo.cmsPropertyType AS MEMBTYPES ON MEMBTYPES.contentTypeId = MEMBLST.contentType LEFT OUTER JOIN
dbo.cmsPropertyData AS MEMBDATA ON MEMBDATA.contentNodeId = MEMBLST.nodeId AND
MEMBDATA.propertytypeid = MEMBTYPES.id LEFT OUTER JOIN
dbo.cmsMember AS MEMB ON MEMB.nodeId = MEMBLST.nodeId
WHERE (MEMBLST.nodeId IS NOT NULL)
My SQL skills are fairly basic, so hope someone can help
我的 SQL 技能相当基础，所以希望有人可以提供帮助
~~~~~~~~~~~~ WORKING CODE ~~~~~~~~~~~~~
~~~~~~~~~~~~ 工作代码~~~~~~~~~~~~~~~
Managed to get it working and here is the code
设法让它工作，这是代码
SELECT MEMBTYPEID.text AS MemberType, MEMBLST.nodeId, MEMBTYPES.Name AS MemberField, MEMBTYPES.Alias AS MemberFieldAlias, MEMB.LoginName,
ISNULL(CASE
WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Nvarchar') THEN MEMBDATA.[dataNvarchar]
WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Ntext') THEN MEMBDATA.[dataNtext]
WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Date') THEN CONVERT(NVARCHAR, MEMBDATA.[dataDate])
WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Integer') THEN CONVERT(NVARCHAR, MEMBDATA.[dataInt])
ELSE NULL END, NULL)
AS MemberData
FROM
(SELECT id, text FROM dbo.umbracoNode WHERE (nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43')) AS MEMBTYPEID
LEFT OUTER JOIN (SELECT nodeId, contentType FROM dbo.cmsContent) AS MEMBLST ON MEMBLST.contentType = MEMBTYPEID.id
LEFT OUTER JOIN dbo.cmsPropertyType AS MEMBTYPES ON MEMBTYPES.contentTypeId = MEMBLST.contentType
LEFT OUTER JOIN dbo.cmsPropertyData AS MEMBDATA ON MEMBDATA.contentNodeId = MEMBLST.nodeId AND MEMBDATA.propertytypeid = MEMBTYPES.id
LEFT OUTER JOIN dbo.cmsMember AS MEMB ON MEMB.nodeId = MEMBLST.nodeId
WHERE (MEMBLST.nodeId IS NOT NULL)
回答by Sem Vanmeenen
A collation is basically a codepage that tells sql how to interpret/compare/sort strings . It can be for example be case (in)sensitive or (not) ignore accents (like ^ in French). Fore more info, see here.
归类基本上是一个代码页，它告诉 sql 如何解释/比较/排序字符串。例如，它可以区分大小写（不区分大小写）或（不）忽略重音符号（如法语中的 ^）。有关更多信息，请参见此处。
In sql server, you set the collation on the server/database/column level where each lower level can override the default of the higher level. That makes it possible that you will compare strings from two different collations. And that's where the catch. Sometimes it's impossible to compare 2 different collations. For example, if column 1 has a case insensitive collation and column 2 a case sensitive and you compare 'AAA' from column 1 with 'aaa' from column 2, are they equal or not ? In such a case, sql throws a collation error.
在 sql server 中，您在服务器/数据库/列级别设置排序规则，其中每个较低级别可以覆盖较高级别的默认值。这使您可以比较来自两个不同排序规则的字符串。这就是问题所在。有时无法比较 2 种不同的排序规则。例如，如果第 1 列的排序规则不区分大小写，第 2 列的排序规则区分大小写，并且您将第 1 列中的“AAA”与第 2 列中的“aaa”进行比较，它们是否相等？在这种情况下，sql 会引发整理错误。
column 4refers to your MemberData column i.e. the giant ISNULL(Case ...statement. Sql collation conflicts typically happen with compares between strings so that means one of your IN or = operators is the culprit. To debug, I would gradually remove parts of that statement until the error doesn't happen anymore. Then check the collation of the columns in the part you just removed. If they are different, it's very likely that's your problem.
column 4指的是您的 MemberData 列，即巨型ISNULL(Case ...声明。Sql 排序规则冲突通常发生在字符串之间的比较中，这意味着您的 IN 或 = 运算符之一是罪魁祸首。为了调试，我会逐渐删除该语句的部分内容，直到错误不再发生。然后检查您刚刚删除的部分中列的排序规则。如果它们不同，那很可能是您的问题。
You then could use COLLATEto force the string of one of those columns to cast to the collation of the other column. Be warned however that depending on your collation you can get weird compare results i.e. '?' can be equal to 'a' or not. I would try to pin down which strings in your columns are giving the collation error so that you can see what would be the effect from using COLLATE.
然后，您可以使用COLLATE强制将其中一列的字符串转换为另一列的排序规则。但是请注意，根据您的排序规则，您可能会得到奇怪的比较结果，即“？” 可以等于或不等于 'a'。我会尝试确定您的列中的哪些字符串给出了整理错误，以便您可以看到使用 COLLATE 会产生什么影响。
As a general rule, I would recommend to never override the default collation of your database just to prevent this sort of headaches.
作为一般规则，我建议永远不要为了防止这种麻烦而覆盖数据库的默认排序规则。
回答by Jen Stirrup
For the fourth column, I suspect that something either side of the '=' is a different collation. You could try putting 'collate database_default' either side of the '='. If you try 'SP_HELP' on each of the table names used by the fourth column, this will help you to see if there are any differences in the collation at the column level. It's not the type of data that's the issue, it's the type of collation of the column. As SemVanmeenen has said above, for the query affecting the 4th column, try to remove some of the parts of the statement and try to run it, and then put them in again. That might help you to identify the offending line of code. Good luck! Please let us know how you get on. Regards, Jen Stirrup www.jenstirrup.com
对于第四列，我怀疑 '=' 两侧的某些内容是不同的排序规则。您可以尝试将 'collate database_default' 放在 '=' 的任一侧。如果您在第四列使用的每个表名上尝试“SP_HELP”，这将帮助您查看列级别的排序规则是否存在任何差异。问题不是数据类型，而是列的整理类型。正如SemVanmeenen上面所说的，对于影响第4列的查询，尽量去掉语句的一些部分并尝试运行它，然后再把它们放进去。这可能会帮助您识别有问题的代码行。祝你好运！请告诉我们您的进展情况。问候， Jen Stirrup www.jenstirrup.com
回答by Etienne
Do you have "collate" columns in your tables ?
你的表中有“整理”列吗？
If true you should add "collate" after their names. As an exemple if cmsPropertyType.contentTypeId is a collate column. Instead of : ON MEMBTYPES.contentTypeId = MEMBLST.contentType
如果为真，您应该在他们的名字后添加“collate”。例如，如果 cmsPropertyType.contentTypeId 是整理列。而不是：ON MEMBTYPES.contentTypeId = MEMBLST.contentType
You should write : ON MEMBTYPES.contentTypeId collate 'contentTypeIdCollateAlias' = MEMBLST.contentType
你应该写： ON MEMBTYPES.contentTypeId collate 'contentTypeIdCollateAlias' = MEMBLST.contentType
Check out documentation : Doc on MSDN
查看文档：MSDN 上的文档
And read the SemVanmeen's post for a better understanding of your probleme
并阅读 SemVanmeen 的帖子以更好地了解您的问题
回答by Andriy M
Two suspects.
两名嫌疑人。
First one is this:
第一个是这样的：
SELECT value
FROM [dbo].[cmsDataTypePreValues]
WHERE datatypenodeid = MEMBTYPES.[dataTypeId] AND id = CONVERT(INT, MEMBDATA.[dataInt])
What type is value? It should probably be nvarchar, otherwise you should convert it accordingly.
什么类型value？它可能应该是nvarchar，否则你应该相应地转换它。
Second one is the default ''in the ISNULL. Maybe you should define it as N''?
第二个是默认''的ISNULL。也许你应该把它定义为N''？
Also, regarding the subselect, why does it have to be repeated twice, in WHEN, and afterwards in THEN? You could replace that sub-CASEcompletely with COALESCE((SELECT value...), CONVERT(NVARCHAR, MEMBDATA.[dataInt])).
另外，关于子选择，为什么它必须重复两次，在WHEN，然后在THEN？你可以CASE用COALESCE((SELECT value...), CONVERT(NVARCHAR, MEMBDATA.[dataInt])).
