按单位对包含货币的列进行排序。

Question

我在SQL中有一个与股票相关的表，其中一个列是"Market“。该列为"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

有人能建议一个查询来解决这个问题吗？

提前谢谢你。

Answer 1

将patindex()与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