ORDER BY的货币值 [英] ORDER BY for currency values

问题描述

我在一个MySQL表中有一个priest_value列，现在我需要基于此进行排序.该列实际上是VARCHAR，并附带有货币符号.但并非所有的价值.货币符号可以是USD(美元符号)，POUND或INR(卢比符号).因此，当前的订购者无法正常运行.如何在不手动删除货币符号的情况下解决此问题?

I have a prize_value column in one of the MySQL tables and now I need to do sorting based on this. This column is actually VARCHAR and has currency symbols attached with it. But not for all the values. The currency symbol can be USD(dollar sign), POUND or INR (Rupee Symbol). So currently the order by is not working properly. How can I fix this without removing the currency symbol manually ?

以下是列中的一些示例值:

Here are some sample values in the column:

.50
£10
£100
$15
$20
£25
£50
10
₹30

推荐答案

您需要两列:

• 一个值，可以排序的浮点/双精度(或整数)，用于求和等操作中.
• 一种用于其他货币(一种遵循 ISO 4217标准的字符(3))目的(显示，也许是转化等)

• one for the value, a float/double (or an integer) that can be ordered, used in operations such as sums, etc.
• one for the currency (a char(3) following ISO 4217 standard) used for other purposes (display, maybe conversion, etc.)

将值和符号存储在varchar中是没有意义的，尤其是由于货币符号的位置因国家/地区而异(可以在开头或结尾)，因此在符号和符号之间可能会有空格.值，否则，等等.

Storing both value and symbol inside a varchar is non-sense, especially since the position of the currency symbol varies from country to country (can be at the beginning or at the end), there might be a space between the symbol and the value, or not, etc.

话虽如此，如果您不想更改表，类似的事情可能会起作用:

That being said, if you don't want to change your table, something like that could work:

ORDER BY CAST(
  REPLACE(REPLACE(REPLACE(thecolumn,'$',''),'£',''),'₹','')
  AS DECIMAL(10,2)
)

这篇关于ORDER BY的货币值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！