在Oracle查询中为1000000至1M和1000至1K [英] 1000000 to 1M and 1000 to 1K in oracle query

问题描述

我想通过使用oracle查询显示以下数字:

i want to show numeric digits as given below by using an oracle query:

1000000  1M
  22000 22k

请帮助，在Oracle查询中有什么办法?

Please help is there any way to do it in oracle query??

推荐答案

我认为没有标准功能(科学记数法除外)，但是您可以自己定义这样的功能:

I don't think there's a standard function (except for the scientific notation), but you can define such a function yourself:

SQL> WITH DATA AS (SELECT power(10, ROWNUM) num FROM dual CONNECT BY LEVEL <= 9)
  2  SELECT num,
  3         CASE
  4            WHEN num >= 1e6 THEN
  5             round(num / 1e6) || 'M'
  6            WHEN num >= 1e3 THEN
  7             round(num / 1e3) || 'k'
  8            ELSE to_char(num)
  9         END conv
 10    FROM DATA;

       NUM CONV
---------- -----------------------------------------
        10 10
       100 100
      1000 1k
     10000 10k
    100000 100k
   1000000 1M
  10000000 10M
 100000000 100M
1000000000 1000M

这篇关于在Oracle查询中为1000000至1M和1000至1K的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！