SQL Server枢轴订购问题
问题描述:
我的查询返回一个数据透视表,第一列(nummnth)包含01,02,03月份的月份,一月,二月,三月的文本值。问题在于排序为01,03, 02而不是01,02,03。SQL Server枢轴订购问题
我该如何解决这个问题?
查询是:
select [nummnth] ,[mnth],[Business Fixed Score],[Business Fixed
Sessions],[Business Mobile Score],[Business Mobile Sessions],[Business
Merged Score],[Business Merged Sessions] from (Select [nummnth],
[Mnth],C.* from (
SELECT [Service],nummnth,mnth,b.A2 as [User_Score],b.A2_Sessions as
[Sessions_Count] FROM [QTDB].[dbo].[QTD_BOX_BUS_MERGED_CP] as [b] where
YR=2017
and [service] = 'Business Fixed' and Agent='ANAME'
Union
SELECT [Service],nummnth,mnth,b.A2 as [User_Score],b.A2_Sessions as
[Sessions_Count]
FROM [QTDB].[dbo].[QTD_BOX_BUS_MERGED_CP] as [b] where YR=2017 and
[service] = 'Business Mobile' and Agent='ANAME'
UNION all
SELECT 'Business Merged' as [Service] ,nummnth,mnth,b.A2 as
[User_Score],b.A2_Sessions as [Sessions_Count]
FROM [QTDB].[dbo].[QTD_BOX_BUS_AGENT_MNTH_MERGED] as [b] where YR=2017
and Agent='ANAME') A
Cross Apply (Values (A.[Service]+' Score',
cast(A.[User_Score] as float)),(A.[Service]+' Sessions',cast(A.
[Sessions_Count] as float))) C (Item,Value)) R Pivot (
sum(Value) For [Item] in ([Business Fixed Score],
[Business Fixed Sessions],
[Business Mobile Score],[Business Mobile Sessions],
[Business Merged Score],[Business Merged Sessions])) PV
答
在SQL-服务器没有隐含的秩序!没有,从来没有...
你甚至不能说问题是,排序是01,03,02而不是01,02,03。下一次调用可能会返回不同的结果。
确保订单的唯一方法是在最外面的查询中输入ORDER BY
!
只需添加ORDER BY [nummnth]
作为最后一行并检查结果。
我在查询中的所有地方尝试了ORDER BY [nummnth],除了在PV之后的最后。现在完美的工作..我觉得很愚蠢,但这是一个“编程到极端”的时期。 –
@AndyL。我很高兴,你可以解决你的问题。别介意,每个人都会不时地感到愚蠢: - D – Shnugo