使用 CASE WHEN 按两列排序

2021-09-10 00:00:00 tsql sql-server

我有一个这样的查询:

SELECT 
    ROW_NUMBER() OVER(ORDER BY USER_FNM, USER_LNM) AS ROW_NUM,
    USER_TEL, USER_FAX, USER_MOB            
FROM 
    BAUSER      
ORDER BY            
    CASE 
       WHEN @cOrderBy = 'USER_FNM_USER_LNM ASC' THEN USER_LNM 
    END ASC,
    CASE 
       WHEN @cOrderBy = 'USER_FNM_USER_LNM DESC' THEN USER_LNM 
    END DESC,
    CASE 
       WHEN @cOrderBy IS NULL THEN USER_KEY 
    END ASC
    OFFSET @iStartIndex ROWS
    FETCH NEXT @iRowsPerPage ROWS ONLY

我想做的是按两列排序 - 但这显示语法错误:

What I would like to do is sorting by two columns - but this is showing syntax error:

CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM ASC' THEN USER_LNM, USER_FNM END ASC

知道在这种情况下如何按两列排序吗?

Any idea how to sort by two columns in this case?

推荐答案

CASE 是一个返回单个表达式/值的表达式.您需要为每一列编写一个 CASE 语句:

CASE is an expression that returns a single expression/value. You need to write one CASE statement per column:

ORDER BY CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM ASC' THEN USER_LNM END ASC, CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM DESC' THEN USER_LNM END DESC, CASE WHEN @cOrderBy IS NULL THEN USER_KEY END ASC, CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM ASC' THEN USER_FNM END ASC, CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM DESC' THEN USER_FNM END DESC

更新(反映更新的问题)

由于您有 ROW_NUM 列，您可以按此排序:

Since you have ROW_NUM column, you can sort by that:

CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM ASC' THEN ROW_NUM END ASC, CASE WHEN @cOrderBy = 'USER_FNM_USER_LNM DESC' THEN ROW_NUM END DESC, CASE WHEN @cOrderBy IS NULL

相关文章