按条款动态排序

debugcn 发表于 Dev

阿比吉斯

在下面的查询中，我在这里按条款使用了动态订单-

        DECLARE @employee TABLE
        ( 
            ID INT IDENTITY(1,1)
            ,Name VARCHAR(50)
            ,Gender VARCHAR(20)
            ,DOB DATETIME
            ,TotProjects INT
        )


        INSERT INTO @employee
        (   
            Name
            ,Gender 
            ,DOB 
            ,TotProjects
        )
        VALUES 
        ('Mike', 'Male', '07/26/1986', 3)
        ,('Neil' , 'Male', '02/21/1990', 2)
        ,('Jessica' ,'FeMale','04/15/1988', 9)
        ,('Tony','Male','03/18/1987', 6)
        ,('Adam','Male','01/16/1983', 12)


        DECLARE @searchCol VARCHAR(50) = 'PROJECTS' 
        DECLARE @direction VARCHAR(5) = 'ASC'   

        SELECT *
        FROM @employee
        ORDER BY 
                CASE WHEN @direction = 'DESC'
                THEN
                CASE 
                    WHEN @searchCol = 'NAME' THEN Name 
                    WHEN @searchCol = 'GENDER' THEN Gender
                    WHEN @searchCol = 'DOB' THEN CAST (DOB AS VARCHAR(20)) 
                    WHEN @searchCol = 'PROJECTS' THEN ABS(TotProjects)
                END 
            END DESC,
            CASE WHEN @direction = 'ASC'
                THEN
                CASE 
                    WHEN @searchCol = 'NAME' THEN Name 
                    WHEN @searchCol = 'GENDER' THEN Gender
                    WHEN @searchCol = 'DOB' THEN CAST (DOB AS VARCHAR(20)) 
                    WHEN @searchCol = 'PROJECTS' THEN CAST(TotProjects AS VARCHAR(20))
                END
            END ASC



        --- Ends Here -----

因此，在上面的查询中，我从一个名为-@direction的变量获取了按方向排序，然后我将传递变量-@searchCol来说要对我的哪一列进行排序。

现在，如果我通过-“名称”或“性别”，则其排序就很好了，因为它们都是VARCHAR列，但是如果我要对“项目”或“ DOB”进行排序，则排序方式不正确。（可能是它的ascii值）。当我通过Direction-@direction-“ ASC”和@searchCol-“ Projects”时，结果很奇怪。基本上，它不会基于“项目”和“ DOB”进行排序，因为它们分别是“数字”和“日期时间”字段。

钙，请您对此有所了解。

不

我将以不同的方式实现动态排序。我相信通过这样做，数据库引擎将能够更有效地使用索引（前提是您已拥有索引）

 SELECT *
    FROM @employee
    ORDER BY 
    CASE WHEN @direction = 'DESC' AND @searchCol = 'NAME' THEN Name ELSE ' ' END DESC,
    CASE WHEN @direction = 'DESC' AND @searchCol = 'GENDER' THEN Gender ELSE ' ' END DESC,
    CASE WHEN @direction = 'DESC' AND @searchCol = 'DOB' THEN DOB ELSE CAST(0 AS DATE) END DESC,
    CASE WHEN @direction = 'DESC' AND @searchCol = 'PROJECTS' THEN TotProjects ELSE 0 END DESC,
    CASE WHEN @direction = 'ASC' AND @searchCol = 'NAME' THEN Name ELSE ' ' END,
    CASE WHEN @direction = 'ASC' AND @searchCol = 'GENDER' THEN Gender ELSE ' ' END,
    CASE WHEN @direction = 'ASC' AND @searchCol = 'DOB' THEN DOB ELSE CAST(0 AS DATE) END,
    CASE WHEN @direction = 'ASC' AND @searchCol = 'PROJECTS' THEN TotProjects ELSE 0 END

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。