sql-server - SQL Union All查询中的排序规则冲突

Question

有一个Union All查询，如下所示。在SQL Server中触发时，出现错误


“无法解决SELECT语句中列1的排序规则冲突。”


请在哪里将此联合所有查询的Collat​​e database_default语句添加到哪里？

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

Answer 1

通常在JOIN或WHERE条件之后，无论何时比较不同归类的字符串都需要指定归类。使用UNION，将比较每个SELECT语句中的列，以确保可以进行合并。您的错误告诉您它是第1列，因此您必须在其中指定排序规则。
试试下面

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