SQL Server按问题顺序处理jquery数据表

Question

我编写了以下脚本来模拟数据表中传入的参数，并尝试使用这些参数过滤结果。除了按条款排序外，一切都很好。基本上，它只按行号排序，而不考虑按列提供第二个顺序的case语句。

declare @sSortColumn as nvarchar(50)='Country';
declare @sSortDirection as nvarchar(5) = 'Desc';
declare @sSearch as nvarchar(50) = '';
declare @iDisplayLength as int = 20;
declare @iDisplayStart as int = 20;
declare @sIDsearch as int = CASE WHEN ISNUMERIC(@sSearch) = 1 THEN CAST(@sSearch AS INT) ELSE 0 END;
WITH media AS
(
   select ROW_NUMBER() OVER (ORDER BY mc.id) as RowNumber,
   mc.id,mc.Name, mc.CityID,lc.Name as Country
     from Lookup_MediaChannels mc
    left join Lookup_SonarMediaTypes st on mc.SonarMediaTypeID = st.ID
    left join Lookup_SonarMediaGroups sg on sg.ID = st.SonarMediaGroupID
    left join Lookup_MediaTypes mt on mc.MediaTypeID = mt.ID
    left join Lookup_SonarMediaGroups sg1 on sg1.ID = mt.MediaGroupID
    left join lookup_Countries lc on lc.id = mc.countryid
    where mc.Name like '%'+@sSearch+'%'
    and (sg1.ID=1 or sg.ID =1 )
    or mc.id =  @sIDsearch
) 
SELECT RowNumber, Name, Country
FROM media 
WHERE RowNumber BETWEEN (@iDisplayStart+ 1) AND (@iDisplayStart+ @iDisplayLength)
order by rownumber, 
CASE WHEN @sSortColumn = 'Name' AND @sSortDirection = 'Desc' 
THEN Name END DESC,
CASE WHEN @sSortColumn = 'Name' AND @sSortDirection != 'Desc' 
THEN Name END,
CASE WHEN @sSortColumn = 'Country' AND @sSortDirection = 'Desc' 
THEN Country END DESC,
CASE WHEN @sSortColumn = 'Country' AND @sSortDirection != 'Desc' 
THEN Country END

Answer 1

这个简化的示例可以帮助您

http://sqlfiddle.com/#！6/35ffb/10

设置一些伪数据（这将被您的选择语句所取代）

create table x(
  id int,
  name varchar(3),
  country varchar(2)
)
insert into x 
values (1,'aaa','uk'),
(2,'aaa','us'),
(3,'baa','uk'),
(4,'caa','uk'),
(5,'baa','it')

一些变量用于保存排序字段和排序顺序

declare @so char(1)
declare @sf char(1)
set @so = 'a' -- a = asc  d = desc
set @sf = 'n' -- n = name c = country

以及选择返回排序数据

  SELECT row_number() 
  OVER (order by 
          CASE WHEN @so =  'd' THEN sf END desc, 
          CASE WHEN @so <> 'd' THEN sf end,
          id
        ) AS aa, name,country
  FROM (
    SELECT x.*, case @sf when 'n' then name when 'c' then country end sf 
    FROM X
  ) X