我想有寻呼＆动态orderby在存储过程中条款（太ON多于一列）。 我曾尝试以下，但给我一个错误
看来我不能使用ROW_NUMBER（）以上等级（）
窗函数不能在另一个窗口函数或聚合的上下文中使用。
是否有任何替代来实现这一个，除了LINQ to SQL的
SELECT [t8].[AssetId],
[t8].[WorkOrderId],
[t8].[IssueDescription] AS [WorkOrderDescription],
[t8].[value] AS [Type],
[t8].[WorkOrderStatusTypeName] AS [Status],
[t8].[value2] AS [StartDate],
[t8].[CompletedDate] AS [CompleteDate],
[t8].[value22] AS [CompletedBy],
ISNULL([t8].[value3],0) AS [Hours]
FROM (
SELECT ROW_NUMBER()
OVER (ORDER BY CASE WHEN @sortColumnName = 'default' THEN (RANK() over( order by [t7].[WorkOrderStatusTypeId] ASC, [t7].[WorkOrderId])) END ,
CASE WHEN @sortColumnName = 'WorkOrderId' AND @sortOrder = 'asc' THEN [t7].[WorkOrderId] END ASC,
CASE WHEN @sortColumnName = 'WorkOrderId' AND @sortOrder = 'desc' THEN [t7].[WorkOrderId] END DESC
) AS [ROW_NUMBER],
[t7].[AssetId],
[t7].[WorkOrderId],
[t7].[IssueDescription],
[t7].[value],
[t7].[WorkOrderStatusTypeName],
[t7].[value2],
[t7].[CompletedDate],
[t7].[value22],
[t7].[value3]
from --Different tables
) as t8
WHERE [t8].[ROW_NUMBER] BETWEEN ((@pageIndex-1) * @pageSize)+ 1 AND @pageIndex * @pageSize
ORDER BY [t8].[ROW_NUMBER]
用过的
SELECT CASE
WHEN @sortColumnName ='default' AND @sortOrder = 'asc' then row_number() over (order by [t7].[WorkOrderStatusTypeId], [t7].[CompletedDate] ASC)
WHEN @sortColumnName ='WorkOrderId' AND @sortOrder = 'asc' then row_number() over (order by [t7].[WorkOrderId] ASC)
WHEN @sortColumnName ='WorkOrderId' AND @sortOrder = 'desc' then row_number() over (order by [t7].[WorkOrderId] DESC)
END AS [ROW_NUMBER],
代替
SELECT ROW_NUMBER()
OVER (ORDER BY CASE WHEN @sortColumnName = 'default' THEN (RANK() over( order by [t7].[WorkOrderStatusTypeId] ASC, [t7].[WorkOrderId])) END ,
CASE WHEN @sortColumnName = 'WorkOrderId' AND @sortOrder = 'asc' THEN [t7].[WorkOrderId] END ASC,
CASE WHEN @sortColumnName = 'WorkOrderId' AND @sortOrder = 'desc' THEN [t7].[WorkOrderId] END DESC
) AS [ROW_NUMBER],
不能有嵌套的窗口功能，如ROWNUMBER（）和排名（）一起，相反，他们可以case语句中使用。
好像你想有一个默认顺序是由两列和您尝试使用RANK()在试图把两个默认的排序标准，在一种情况下。
显然，这是不可能的。 一个显而易见的选择似乎两列被分成两种情况，重复@sortColumnName = 'default'状态：
ROW_NUMBER()
OVER (ORDER BY CASE WHEN @sortColumnName = 'default' THEN [t7].[WorkOrderStatusTypeId] END ASC,
CASE WHEN @sortColumnName = 'default' THEN [t7].[WorkOrderId] END ASC,
CASE WHEN @sortColumnName = 'WorkOrderId' AND @sortOrder = 'asc' THEN [t7].[WorkOrderId] END ASC,
CASE WHEN @sortColumnName = 'WorkOrderId' AND @sortOrder = 'desc' THEN [t7].[WorkOrderId] END DESC
在另一方面，它可以很容易地看出，第二和第三种情况可以合并成一个，因为相同的表达是由两当相应的条件为真返回。 所以，这里是你能做什么，而不是：
ROW_NUMBER()
OVER (ORDER BY CASE WHEN @sortColumnName = 'default' THEN [t7].[WorkOrderStatusTypeId] END ASC,
CASE WHEN @sortColumnName = 'default' OR @sortColumnName = 'WorkOrderId' AND @sortOrder = 'asc' THEN [t7].[WorkOrderId] END ASC,
CASE WHEN @sortColumnName = 'WorkOrderId' AND @sortOrder = 'desc' THEN [t7].[WorkOrderId] END DESC
正如你所看到的，第二种情况是用于设置排序WorkOrderId ASC都当在参数明确指定列，并在'default'的规定。
当创建Web API，做动态排序，尤其是多colmuns的唯一途径，是构建SQL字符串，并使用内置的sprop“sp_executesql的”运行它。 请参阅下面我的解决方案例如，它返回一个分页表，由多个列的字符串命令，并返回总行通过输出参数计数。
请不是使用直接连接起来这样的字符串时，必须先检查是否有恶意代码的SQL注入的参数。 在这里，我必须检查SEARCHTERM和SortTerm
alter PROCEDURE [dbo].[usp_Ranking_SearchForPlayerByName]
@SearchTerm varchar(100),
@WeekNumber int,
@Gender nchar(1),
@SortTerm nvarchar(100),
@PageSize int,
@PageNumber int,
@TotalCount int OUT
AS
BEGIN
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
DECLARE @PreviousWeek int = dbo.PreviousWeekNo(@WeekNumber)
DECLARE @Offset int = (@PageNumber - 1) * @PageSize
DECLARE @sql nvarchar(max) = N'Select
h.PlayerID
,p.Forename as FirstName
,p.Surname as Lastname
,p.Gender as Gender
,''return country of residence'' as Country
,r.Name as Region
,h.Position as RankThisWeek
,previousWk.Position as RankLastWeek
,(previousWk.Position - h.Position) as Change
From PlayerHistories h
left outer join
(Select * from PlayerHistories
where WeekNumber = @PreviousWeek) previousWk on h.PlayerId = previousWk.PlayerId
inner join Players p on h.PlayerId = p.Id
left join Countries c on p.CountryId = c.Id
left join Regions r on c.RegionId = r.Id
Where h.WeekNumber = @WeekNumber And
p.Gender = CASE When @Gender = ''M'' Then ''M'' When @Gender = ''F'' Then ''F'' Else p.gender End And
p.FullName like ''%' + @SearchTerm + '%''
Order by ' + @SortTerm + '
OFFSET @offset ROWS FETCH NEXT @PageSize ROWS ONLY
'
EXEC SP_EXECUTESQL @Sql,
N'@PreviousWeek int, @WeekNumber int, @Gender nchar(1), @offset int, @PageSize int',
@PreviousWeek, @WeekNumber, @Gender, @offset, @PageSize;
SELECT @TotalCount =
(SELECT Count(h.Id)
From PlayerHistories h
left outer join
(Select * from PlayerHistories
where WeekNumber = @PreviousWeek) previousWk on h.PlayerId = previousWk.PlayerId
inner join Players p on h.PlayerId = p.Id
Where h.WeekNumber = @WeekNumber And
p.Gender = CASE When @Gender = 'M' Then 'M' When @Gender = 'F' Then 'F' Else p.gender End And
p.FullName like '%' + @SearchTerm + '%');
END
测试调用这个使用下面的语句：
DECLARE @op int
exec dbo.usp_Ranking_SearchForPlayerByName 'smith',201715,'','LastName DESC, Gender, FirstName',10,1, @op OUTPUT
select @op
