order by 排序的数字异常

order by  在排序的时候尽量查询少量的字段和查询长度较小的字段，否则会影响 排序

 

 

               --SELECT  *
    --INTO    ##CTEC
    --FROM    ( SELECT   * ,
    --                        --ROW_NUMBER() OVER ( ORDER BY CASE WHEN INST_DEADLINE IS NULL
    --                        --                                  THEN WS_ID
    --                        --                                  ELSE ''
    --                        --                             END DESC ) AS ORDER_NUM 
    --                        --                                 ,
    --                        ROW_NUMBER() OVER ( ORDER BY CASE @sortdirection
    --                                                       WHEN 'ASC'
    --                                                       THEN CASE @sortby
    --                                                          WHEN 'DEADLINE'
    --                                                          THEN ISNULL(INST_DEADLINE,
    --                                                          '3099-1-1')
    --                                                          END
    --                                                     END ASC, CASE @sortdirection
    --                                                          WHEN 'DESC'
    --                                                          THEN CASE @sortby
    --                                                          WHEN 'DEADLINE'
    --                                                          THEN INST_DEADLINE
    --                                                          END
    --                                                          END DESC, CASE @sortdirection
    --                                                          WHEN ''
    --                                                          THEN CASE @sortby
    --                                                          WHEN ''
    --                                                          THEN INST_DEADLINE
    --                                                          END
    --                                                          END DESC ) AS NUM
    --               FROM     ( SELECT    *
    --                          FROM      ##CTEB
    --                        ) T_CTEB
    --               WHERE    1 = 1
    --                        --AND numid = 1
                       
    --        ) AS T5;  


    DECLARE @orderby VARCHAR(50)= @sortby + ' ' + @sortdirection;

    PRINT @orderby
    SELECT  *
    INTO    ##CTEC
    FROM    ( SELECT    * ,
                        ROW_NUMBER() OVER ( ORDER BY CASE WHEN @orderby = 'DEADLINE DESC'
                                                          THEN INST_DEADLINE
                                                     END DESC, CASE
                                                              WHEN @orderby = 'DEADLINE ASC'
                                                              THEN INST_DEADLINE
                                                              END ASC, CASE
                                                              WHEN @orderby = 'ID DESC'
                                                              THEN ID
                                                              END DESC, CASE
                                                              WHEN @orderby = 'ID ASC'
                                                              THEN ID
                                                              END ASC, CASE
                                                              WHEN @orderby = 'ADDRESS DESC'
                                                              THEN ADDRESS
                                                              END DESC, CASE
                                                              WHEN @orderby = 'ADDRESS ASC'
                                                              THEN ADDRESS
                                                              END ASC , CASE
                                                              WHEN @orderby = 'TITLE DESC'
                                                              THEN TITLE
                                                              END DESC, CASE
                                                              WHEN @orderby = 'TITLE ASC'
                                                              THEN TITLE
                                                              END ASC , CASE
                                                              WHEN @orderby = 'CONTENT DESC'
                                                              THEN CONTENT
                                                              END DESC, CASE
                                                              WHEN @orderby = 'CONTENT ASC'
                                                              THEN CONTENT
                                                              END ASC , CASE
                                                              WHEN @orderby = 'CREATE_TIME DESC'
                                                              THEN CREATE_TIME
                                                              END DESC, CASE
                                                              WHEN @orderby = 'CREATE_TIME ASC'
                                                              THEN CREATE_TIME
                                                              END ASC ) AS RowNum
              FROM      ##CTEB
              WHERE     1 = 1
            ) AS T5;