社区首页 >问答首页 >按月排序，然后按MonthYear为字符串SQL的年份进行排序。

问按月排序，然后按MonthYear为字符串SQL的年份进行排序。
EN

Stack Overflow用户

提问于 2014-11-12 12:29:27

回答 1查看 103关注 0票数 0

declare @ActivityYearStart int = 2014
declare @ActivityYear2 int = (@ActivityYearStart + 2)
declare @ActivityYearEnd int = 2015
declare @ActivityYearM1 int = (@ActivityYearEnd - 1)
declare @ActivityMonth int = 1
declare @ActivityMonth1 int = 3

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)  


select @cols = STUFF((SELECT distinct ',' + QUOTENAME((CONVERT(varchar(5), ActivityMonth) + '-' 
                + CONVERT(varchar(5), ActivityYear))) As MonthYear
            FROM Forecast
            WHERE((ActivityYear = @ActivityYearStart)
              AND (ActivityMonth between  @ActivityMonth and 12))
              OR ((ActivityYear between @ActivityYear2 and @ActivityYearM1)
              AND (ActivityMonth between 1 and 12))
              OR ((ActivityYear = @ActivityYearEnd) 
              AND (ActivityMonth between 1 and @ActivityMonth1))
                     AND Category = 'Forecast'
           Group By Category, ID, ActivityMonth, ActivityYear
           Order By SUBSTRING(MonthYear, 1, 2), SUBSTRING(MonthYear, 2, 4)
        FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = '  
    SELECT Category, ID, ' + @cols + ' FROM
             (
                SELECT Category, ID, (CONVERT(varchar(5), ActivityMonth) + ''-'' 
                + CONVERT(varchar(5), ActivityYear)) As MonthYear, HoursCharged
                FROM Forecast    
            ) x
            PIVOT 
            (
                Sum(HoursCharged)
                for MonthYear in (' + @cols + ')
            ) p '

execute(@query)

因为MonthYear是活动月份和活动年的串联字符串，所以从行到列。我想按月份年排序，但是，它现在是一个字符串，它是一个字符串值，而不是int值。我如何才能把它分解成Order By SUBSTRING(MonthYear, 1, 2), SUBSTRING(MonthYear, 2, 4)。

但我知道错误是：

Msg 207，第16级，状态1，第23线无效的列名‘月’。 Msg 207，第16级，状态1，第23线无效的列名‘月’。 Msg 145，15级，状态1，第12线如果指定select DISTINCT，则按项排序必须出现在选择列表中。

sql-server

sql-order-by

ssms

回答 1

Stack Overflow用户

回答已采纳

发布于 2014-11-12 13:22:18

尝试将@cols声明替换为：

select @cols = STUFF((SELECT ',' + QUOTENAME((CONVERT(varchar(5), ActivityMonth) + '-' 
                + CONVERT(varchar(5), ActivityYear))) As MonthYear
            FROM Forecast
            WHERE((ActivityYear = @ActivityYearStart)
              AND (ActivityMonth between  @ActivityMonth and 12))
              OR ((ActivityYear between @ActivityYear2 and @ActivityYearM1)
              AND (ActivityMonth between 1 and 12))
              OR ((ActivityYear = @ActivityYearEnd) 
              AND (ActivityMonth between 1 and @ActivityMonth1))
                     AND Category = 'Forecast'
           Group By ActivityMonth, ActivityYear
           Order By ActivityYear, ActivityMonth
        FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')