无法解决联合选择中的排序规则冲突 [英] Cannot resolve collation conflict in Union select

问题描述

我有两个查询:

首先不起作用:

select hotels.TargetCode as TargetCode from hotels
union all 
select DuplicatedObjects.duplicatetargetCode as TargetCode 
from DuplicatedObjects where DuplicatedObjects.objectType=4

因为我得到错误:

Cannot resolve collation conflict for column 1 in SELECT statement.

第二部作品:

select hotels.Code from hotels where hotels.targetcode is not null 
union all 
select DuplicatedObjects.duplicatetargetCode as Code 
from DuplicatedObjects where DuplicatedObjects.objectType=4 

结构:

Hotels.Code -PK nvarchar(40)
Hotels.TargetCode - nvarchar(100)

DuplicatedObjects.duplicatetargetCode PK nvarchar(100)

推荐答案

在两个表上使用 sp_help.hotels.TargetCode 上的排序规则与 DuplicatedObjects.duplicateTargetCode 上的排序规则不同，因此 DB 不知道如何处理结果 UNION.

Use sp_help on both tables. The collation on hotels.TargetCode is different from the collation on DuplicatedObjects.duplicateTargetCode, so the DB doesn't know what to do with the resulting UNION.

您可以对其中一个强制执行新的排序规则以匹配另一个，或者将结果放入预定义的临时表/表中，该表/表已经定义了排序规则.

You can force a new collation on one of them to match the other, or put the results into a predefined temp table/table which will have a collation defined already.

您可以使用类似...的内容覆盖现有的排序规则

You can override the existing collation using something like...

DuplicatedObjects.duplicateTargetCode COLLATE SQL_Latin1_General_CP1_CI_AS

...在查询中.这将使用带有排序规则 SQL_Latin1_General_CP1_CI_AS 的重复目标代码.您应该选择与 hotels.TargetCode 匹配的排序规则.

...in the query. This will use the duplicateTargetCode with the collation SQL_Latin1_General_CP1_CI_AS. You should choose a collation which matches that of hotels.TargetCode.

这篇关于无法解决联合选择中的排序规则冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！