如何在 asc 和 desc 中动态排序 2 个 SQL 字段 [英] how to order 2 SQL Fields in asc and desc dynamically

问题描述

我想订购一个 SQL 选择查询，其中有 2 个按顺序排列的字段.然后我需要决定一个是降序，另一个是升序.这是怎么做的

I want to Order a SQL Select Query where there's 2 fields that are in the order by. I then need to decide if one is Descending and the other as Ascending. How is this done

我想要类似的东西:

Select * from Customer
Order By Date @asc_or_Desc_date, Name @asc_or_Desc_name

有人有什么想法吗?

我试过了，但似乎失败了

I have tried this but it seems to fail

SELECT 

    Customer_ID,                        
    Name,                               
    Age                                         

FROM #Customer
ORDER BY 

    CASE WHEN @fieldSort ='Name'
        THEN ROW_NUMBER() over (order by Name) * 
            case when @directionOfSort = 'A' 
                THEN 1 ELSE -1 END,
             ROW_NUMBER() over (order by Age) * 
            case when @directionOfSort = 'A' 
                THEN 1 ELSE -1 END,
        END

有人知道如何排序吗?

推荐答案

您必须动态创建 SQL 语句才能使用变量:

You will have to create your SQL statement dynamically in order to use a variable:

DECLARE @asc_desc VARCHAR(4);

SET @asc_desc = 'DESC';

DECLARE @sql NVARCHAR(1000);

SET @sql = 'Select * from Customer Order By Date ' + @asc_desc + ', Name';

EXEC sp_executesql @sql

这将排序日期DESCENDING和名称ASCENDING.

如果你想使用DESCENDING，你只需要添加DESC，因为ASCENDING是默认的.

You only need to add DESC if you want to use DESCENDING as ASCENDING is default.

这篇关于如何在 asc 和 desc 中动态排序 2 个 SQL 字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！