sql-server - SQL 按子字符串中包含的数字排序

Question

我有这样的数据

Name     SortOrder
-------------------
Lower 1      3
Lower 10      2
Lower 2      1
Lower 1%     6 
Lower 1.5%   5
Lower 3%     4
Average      7 
Upper 1      10
Upper 10      8
Upper 1%     12
Upper 1.5%   11
Average       10  /* sorted 
Average Poor  11  * alphabetically
Rich          12  * only */
Min_Low10     17
Min_Low20     18
Min_Low30     19
Min_Up10      20
Min_Up20      21
Min_Up30      22

我想更新排序顺序，以便当我按它排序时，它会像这样

Name     SortOrder
-------------------
Lower 3%     1
Lower 1.5%   2
Lower 1%     3 
Lower 10      4
Lower 2      5
Lower 1      6
Min_Low30     7
Min_Low20     8
Min_Low10     9
Average       10  /* sorted 
Average Poor  11  * alphabetically
Rich          12  * only */
Min_Up10      13
Min_Up20      14
Min_Up30      15
Upper 1      16
Upper 2      17
Upper 10      18
Upper 1%     19
Upper 1.5%   20

即那些带有数字的子组应该按升序或降序排序(取决于它的下限还是上限)，而任何其他没有数字的子组应该简单地按字母顺序排序并放在中间。

我想我按每个子组的第一个字符进行分组，然后按其转换数字和顺序。但当我尝试转换它时，它给了我一条错误消息:将数据类型 varchar 转换为数字时出错。

SELECT * 
  ,RowNum =
  CASE 
      WHEN Name LIKE 'Lower %[%]' THEN 
          ROW_NUMBER() over (partition by SUBSTRING(Name,1,5) ORDER By CAST(SUBSTRING(Name,7,LEN(Name)-3) as decimal) DESC)
      WHEN Name LIKE 'Lower %' THEN 
          ROW_NUMBER() over (partition by SUBSTRING(Name,1,5) ORDER By SUBSTRING(Name,7,LEN(Name)) DESC)
      WHEN Name LIKE 'Upper %[%]' THEN 
          (ROW_NUMBER() over (partition by SUBSTRING(Name,1,5) ORDER By SUBSTRING(Name,7,LEN(Name)-3)))
      WHEN Name LIKE 'Upper %' THEN 
          (ROW_NUMBER() over (partition by SUBSTRING(Name,1,5) ORDER By SUBSTRING(Name,7,LEN(Name))))

      ELSE -1
  END
  FROM Table1 
  ORDER BY RowNum

这是sqlfiddle

编辑:更改了数据值，因为它们包括 1 和 10，如果它们是 varchar，则会排序错误。

Answer 1

你可以试试这个:

select *
    , RowNum =
     ROW_NUMBER() over (order by 
        case 
            when Name LIKE 'Lower %[%]' then 1
            when Name LIKE 'Lower %' then 2
            when Name LIKE 'Min_Low%' then 3
            when Name LIKE 'Min_Up%' then 5
            when Name LIKE 'Upper %[%]' then 7
            when Name LIKE 'Upper %' then 6
            else 4
        end
        , convert(numeric(6,2), case 
            when Name LIKE 'Lower %[%]' then replace(replace(Name, 'Lower ',''),'%', '')
            when Name LIKE 'Lower %' then replace(Name, 'Lower ','')
            when Name LIKE 'Min_Low%' then replace(Name, 'Min_Low','')
        end) desc
        , convert(numeric(6,2), case 
            when Name LIKE 'Min_Up%' then replace(Name, 'Min_Up','')
            when Name LIKE 'Upper %[%]' then replace(replace(Name, 'Upper ',''),'%', '')
            when Name LIKE 'Upper %' then replace(Name, 'Upper ','')
        end)
        , Name
    ) 
from #Table1

更新

以下是更新声明:

update #Table1 set SortOrder = t2.RowNum
from #Table1 t1
  join (
select *
    , RowNum =
     ROW_NUMBER() over (order by 
        case 
            when Name LIKE 'Lower %[%]' then 1
            when Name LIKE 'Lower %' then 2
            when Name LIKE 'Min_Low%' then 3
            when Name LIKE 'Min_Up%' then 5
            when Name LIKE 'Upper %[%]' then 7
            when Name LIKE 'Upper %' then 6
            else 4
        end
        , convert(numeric(6,2), case 
            when Name LIKE 'Lower %[%]' then replace(replace(Name, 'Lower ',''),'%', '')
            when Name LIKE 'Lower %' then replace(Name, 'Lower ','')
            when Name LIKE 'Min_Low%' then replace(Name, 'Min_Low','')
        end) desc
        , convert(numeric(6,2), case 
            when Name LIKE 'Min_Up%' then replace(Name, 'Min_Up','')
            when Name LIKE 'Upper %[%]' then replace(replace(Name, 'Upper ',''),'%', '')
            when Name LIKE 'Upper %' then replace(Name, 'Upper ','')
        end)
        , Name
    ) 
from #Table1) t2 on t1.Name=t2.Name