我有以下 SP,用于对新闻文章列表进行分页。正如你可能猜到的那样,@count
是要返回的行数,@start
是从中选择行的索引(按内部查询排序),@orderby
指示排序依据的列,并且@orderdir
指示是否对一个方向或另一个方向进行排序。我原来的查询是here,在我添加之前@orderdir
范围。
ALTER PROCEDURE [mytable].[news_editor_paginate]
@count int,
@start int,
@orderby int,
@orderdir int
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (@count) * FROM
(
SELECT ne.*,n.publishstate,
(CASE WHEN @orderdir = 1 THEN
ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @orderby = 0 THEN ne.[time] END DESC,
CASE WHEN @orderby = 1 THEN ne.lastedit END DESC,
CASE WHEN @orderby = 2 THEN ne.title END ASC
)
WHEN @orderdir = 2 THEN
ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @orderby = 0 THEN ne.[time] END ASC,
CASE WHEN @orderby = 1 THEN ne.lastedit END ASC,
CASE WHEN @orderby = 2 THEN ne.title END DESC
)
END
) AS num
FROM news_edits AS ne
LEFT OUTER JOIN news AS n
ON n.editid = ne.id
)
AS a
WHERE num > @start
END
现在实际上没有什么问题,但是@orderby
参数不起作用。如果提供 1 作为@orderdir
参数,它会给我完全相同的结果,就像我提供 2 作为该参数一样。
行号不会对每一行进行计算,但是 case 语句会计算行号,因此无论情况如何,您都只能使用 rownum。
试试这个:
ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @orderby = 0 AND @orderdir = 1 THEN ne.[time] END DESC,
CASE WHEN @orderby = 0 AND @orderdir = 2 THEN ne.[time] END ASC,
CASE WHEN @orderby = 1 AND @orderdir = 1 THEN ne.lastedit END DESC,
CASE WHEN @orderby = 1 AND @orderdir = 2 THEN ne.lastedit END ASC,
CASE WHEN @orderby = 2 AND @orderdir = 1 THEN ne.title END ASC
CASE WHEN @orderby = 2 AND @orderdir = 2 THEN ne.title END DESC
)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)