УПОРЯДОЧЕНИЕ ПО сортировке столбца, состоящего из денег с единицами измерения

#sql-server #sorting #tsql #sql-order-by #sql-server-2019

#sql-сервер #сортировка #tsql #sql-порядок по #sql-server-2019

Вопрос:

У меня есть таблица, связанная с запасами, в SQL с различными столбцами, один из которых — «Рыночная капитализация». Этот столбец имеет тип данных «nvarchar» и имеет такие значения, как 1.16T, 1.2B, 70.324 M, 8B и т.д.

где T = триллион> B = миллиард > M = Миллион

Мне нужно отсортировать эти значения в порядке убывания.

Я попробовал этот запрос:

 select MarketCap from StockSummary
ORDER BY 
    CASE WHEN ISNUMERIC(MarketCap) = 1 THEN 0 ELSE 1 END,
    CASE WHEN ISNUMERIC(MarketCap) = 1 THEN CAST(MarketCap AS float) ELSE 0 END,
    MarketCap DESC

Вывод:

 Market Cap
700.394M
61.816B
6.983B
39.511B
28.996B
231.31M
2.112B
1.23T

Числа сортируются в порядке убывания, но не учитывают единицы измерения (T = триллион> B = миллиард> M = Миллион). Следовательно, не удовлетворяет моим требованиям.

Требуемый вывод:

 Market Cap
1.23T
61.816B
39.511B
28.996B
6.983B
2.112B
700.394M
231.31M

Кто-нибудь может предложить запрос для решения этой проблемы?

Заранее благодарю вас.

1. Измените свою схему и введите нормализованные значения при записи в эту таблицу. Это тот случай, когда numeric на самом деле является числовым, поэтому хранить его в виде текста не очень хорошая идея.

2. Почему вопрос помечен MySQL? он не поддерживает T-SQL.

Ответ №1:

Используя patindex() совместно с a try_convert() , мы можем получить значение, а затем просто CASE разрешить масштаб.

Пример

 Declare @YourTable Table ([Market Cap] varchar(50))  Insert Into @YourTable Values 
 ('700.394M')
,('61.816B')
,('6.983B')
,('39.511B')
,('28.996B')
,('231.31M')
,('2.112B')
,('1.23T')
 
Select *
 From @YourTable
 Order By try_convert(money,left([Market Cap],patindex('%[a-z]%',[Market Cap] 'a')-1))
          * case when [Market Cap] like '%M%' then 1000000 
                 when [Market Cap] like '%B%' then 1000000000 
                 when [Market Cap] like '%T%' then 1000000000000
                 else 1 end Desc

ВОЗВРАТ

 Market Cap
1.23T
61.816B
39.511B
28.996B
6.983B
2.112B
700.394M
231.31M

Редактировать

Просто еще один вариант

 ...
Order By try_convert(float,replace(replace(replace([Market Cap],'M','E6'),'B','E9'),'T','E12')) Desc