SQL Union All Query 中的排序规则冲突 [英] Collation conflict in SQL Union All Query

问题描述

有一个 Union All 查询，如下所示.在 SQL 服务器中触发时，出现错误

There is a Union All query as shown below. When fired in SQL server, I get an error

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

"Cannot resolve collation conflict for column 1 in SELECT statement."

请问，我在哪里添加带有此 Union All 查询的 Collat​​e database_default 语句?

Please, where do I add the Collate database_default statement with this Union All query?

select  OrgCode,null as OrgName,
Q1_A1 as A1Value,Q1_a2 as A2Value,'Quarter 1' as dt,((Q1_A1-Q1_A2)/case when Q1_A1<>0 then Q1_A1 else null end )*100 Percentage
from #Tabl_Quarter_Calculation

union all

select  OrgCode,null as OrgName,Q2_A1 as A1Value,Q2_a2 as A2Value,'Quarter 2' as dt,((Q2_A1-Q2_A2)/case when Q2_A1=0 then null else Q2_A1 end  )*100 Percentage
from #Tabl_Quarter_Calculation

union all

select [OrgCode],[OrgName],a1Value,a2Value,dt
,cast((a1value-a2value)/cast(a1value as real)*100 as varchar(10))+'%' Percentage
 from #Tbl_Display1

union all

--4 week average
select [OrgCode],[OrgName],sum(a1Value) as a1Value,Sum(a2Value) as a2Value,max(dt) as dt
,cast((sum(a1value)-sum(a2value))/cast(sum(a1value) as real)*100 as varchar(10))+'%' Percentage
 from #Tbl_Display1
group by   [OrgCode],[OrgName]
order by 1,5

推荐答案

在比较来自不同排序规则的字符串时，通常需要在 JOIN 或 WHERE 条件之后指定排序规则.使用 UNION，比较每个 SELECT 语句中的列以确保它们可以联合.您的错误告诉您它是第 1 列，因此您必须在此处指定排序规则.试试下面的

Collation needs to be specified wherever strings from different collations are compared, commonly after a JOIN or WHERE condition. With a UNION, the columns in each of the the SELECT statements are compared to ensure they can be unioned. Your error tells you it is column 1, so that is where you must specify the collation. Try the below

select  OrgCode COLLATE database_default,null as OrgName,
Q1_A1 as A1Value,Q1_a2 as A2Value,'Quarter 1' as dt,((Q1_A1-Q1_A2)/case when Q1_A1<>0 then Q1_A1 else null end )*100 Percentage
from #Tabl_Quarter_Calculation

union all

select  OrgCode COLLATE database_default,null as OrgName,Q2_A1 as A1Value,Q2_a2 as A2Value,'Quarter 2' as dt,((Q2_A1-Q2_A2)/case when Q2_A1=0 then null else Q2_A1 end  )*100 Percentage
from #Tabl_Quarter_Calculation

union all

select [OrgCode] COLLATE database_default,[OrgName],a1Value,a2Value,dt
,cast((a1value-a2value)/cast(a1value as real)*100 as varchar(10))+'%' Percentage
 from #Tbl_Display1

union all

---4 week average
select [OrgCode] COLLATE database_default,[OrgName],sum(a1Value) as a1Value,Sum(a2Value) as a2Value,max(dt) as dt
,cast((sum(a1value)-sum(a2value))/cast(sum(a1value) as real)*100 as varchar(10))+'%' Percentage
 from #Tbl_Display1
group by   [OrgCode],[OrgName]
order by 1,5

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