MySql: turn decimal to percent upon viewing the table

问题: I saw a post here in stack overflow with question and answer very similar to my problem. its this post: MySQL: turn decimal into percent within view and as said in the po...

问题:

I saw a post here in stack overflow with question and answer very similar to my problem. its this post: MySQL: turn decimal into percent within view

and as said in the post above, use:

CONCAT(columnname * 100, '%')

and thats what i actually did.. i have a table with a column for percentages for specific ranges of salary. its a salary deduction schedule. i stored the percent values as a decimal datatype in column "ee".

ee | decimal(4,4) | not null

and my problem is its giving me this result and its not what i wanted.. for example, an entry in the column is 0.0200, and i want to show it upon viewing as "2%", but this is what i get.

select s_b,rangeA,rangeB,concat(ee * 100,'%') as 'ee_percent' from htbl;

+___________________________________+
|s_b|  rangeA|   rangeB|ee_percent  |
+---+--------+---------+------------+
|  1|    0.00|  1500.00|   1.0000%  |
|  2| 1500.00|999999.99|   2.0000%  |
+-----------------------------------+

I know that this is a duplicate and an elementary question, but i don't know why its not working, if you see a mistake somewhere in my syntax, or in the data type please tell me. any suggestions and corrections are highly appreciated. Thank you so much.


回答1:

You can use the following solution using FORMAT:

SELECT s_b, rangeA, rangeB, CONCAT(FORMAT(ee * 100, 0), '%') AS 'ee_percent'
FROM htbl;

The result of the calculation is always DECIMAL(4,4). So MySQL show the result with four decimal places. You can use the FORMAT function to format the result or other functions which remove the decimal places.

demo: https://www.db-fiddle.com/f/ncjBpJRwdQVbT7PUBoXgeU/0


回答2:

You can try with Floor function,

select s_b,rangeA,rangeB,concat(FLOOR(ee * 100),'%') as 'ee_percent' from htbl;

回答3:

Use round, i.e:

select s_b,rangeA,rangeB,concat(round(ee * 100),'%') as 'ee_percent' from htbl;
  • 发表于 2019-03-10 18:04
  • 阅读 ( 208 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除