无法解决SELECT语句中第4列的排序规则冲突

Question

我正在尝试执行一些SQL，但是出现以下错误

    Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict for column 4 in SELECT statement.

但是似乎无法弄清楚问题出在哪里？？？任何帮助将不胜感激。

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)

我的SQL技能是相当基本的，因此希望有人可以提供帮助

~ ~ ~工作代码 ~ ~ ~

设法使其正常工作，这是代码

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)

Answer 1

归类基本上是告诉sql如何解释/比较/排序字符串的代码页。例如，可以区分大小写（不区分大小写）或忽略（不忽略）重音（如法语中的^）。有关更多信息，请参见此处。

在sql
server中，您在服务器/数据库/列级别上设置排序规则，其中每个较低级别可以覆盖较高级别的默认值。这样就可以比较两个不同归类的字符串。这就是要抓住的地方。有时无法比较两种不同的归类。例如，如果第1列具有不区分大小写的排序规则，而第2列具有区分大小写的排序，则将第1列中的“
AAA”与第2列中的“ aaa”进行比较，是否相等？在这种情况下，sql会引发排序规则错误。

column 4指的是您的MemberData列，即巨型ISNULL(Case ...语句。字符串之间的比较通常会发生sql排序规则冲突，这意味着罪魁祸首是IN或=运算符之一。为了进行调试，我将逐步删除该语句的某些部分，直到不再发生错误为止。然后检查刚删除的零件中各列的排序规则。如果它们不同，则很可能是您的问题。

然后，您可以使用COLLATE强制将其中一列的字符串强制转换为另一列的排序规则。但是请注意，根据您的排序规则，您可以获得奇怪的比较结果，即“脗”可以等于“
a”，也可以等于“ a”。我会尝试确定列中哪些字符串给出了排序规则错误，以便您可以看到使用COLLATE会带来的影响。

通常，我建议不要为避免此类麻烦而覆盖数据库的默认排序规则。