命名列时的排序规则冲突 [英] Collation conflict when naming columns

问题描述

我遇到了一个（奇怪的？）排序规则冲突的问题（MSSQL2008）。



我的查询的简化示例：

  SELECT  X. * 
， Y.Field3 
  FROM （ SELECT  Field1 
，Field2 
 < span class =code-keyword> FROM  TABLE1）X 
  LEFT   OUTER   JOIN  TABLE2 Y  ON  X.Field1 = Y.Field1 

这很好用（带X. *），但是当我专门写出表X的列时：

  SELECT   X.Field1 
，X.Field2  
，Y。 Field3 
  FROM （ SELECT  Field1 
， Field2 
  FROM  TABLE1）X 
  LEFT   OUTER   JOIN  TABLE2 Y  ON  X.Field1 = Y.Field1 

我收到排序规则冲突错误（无法解决UNION操作中SQL_Latin1_General_CP1_CI_AS和Finnish_Swedish_CI_AS之间的排序规则冲突）



有人可以向我解释如何具体命名列可能导致冲突，或者如何使用*解决这种冲突？



问候，

Johan

解决方案

替换：

  SELECT  X.Field1 
，X.Field2 
，Y.Field3 
  FROM （ SELECT  Field1 
，Field2 
  FROM  TABLE1）X 
  LEFT   OUTER  加入 TABLE2 Y  ON  X.Field1 = Y.Field1 

with：

  SELECT  X.Field1 
，X.Field2 
，Y.Field3 
 < span class =code-keyword> FROM  TABLE1  AS  X  LEFT   OUTER   JOIN  TABLE2 Y  ON  X.Field1 = Y.Field1 

您不需要子查询来加入两个表格！



其次，请看这里：整理优先顺序（Transact-SQL） [ ^ ]解决排序规则问题/问题。

Hi,

I am encountering a (weird?) problem with a collation conflict (MSSQL2008).

A simplified example of my query:

SELECT X.*
      ,Y.Field3
FROM (SELECT Field1
            ,Field2
      FROM TABLE1) X
      LEFT OUTER JOIN TABLE2 Y ON X.Field1 = Y.Field1

This works nicely (with the X.*), but when I specifically write out the columns of table X:

SELECT X.Field1
      ,X.Field2
      ,Y.Field3
FROM (SELECT Field1
            ,Field2
      FROM TABLE1) X
      LEFT OUTER JOIN TABLE2 Y ON X.Field1 = Y.Field1

I get a collation conflict error (Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Finnish_Swedish_CI_AS" in the UNION operation)

Can someone explain to me how specifically naming the columns can cause the conflict, or how using the * "solves" this conflict ?

Regards,
Johan

解决方案

Replace:

SELECT X.Field1
      ,X.Field2
      ,Y.Field3
FROM (SELECT Field1
            ,Field2
      FROM TABLE1) X
      LEFT OUTER JOIN TABLE2 Y ON X.Field1 = Y.Field1

with:

SELECT X.Field1
      ,X.Field2
      ,Y.Field3
FROM TABLE1 AS X LEFT OUTER JOIN TABLE2 Y ON X.Field1 = Y.Field1

You don't need subquery to join two tables!

Secondly, have a look here: Collation Precedence (Transact-SQL)[^] to resolve collation problem/issue.

这篇关于命名列时的排序规则冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！