如何显示每 K 的大数字(代表公斤)? [英] How to show big numbers per K (stands for kilo)?

问题描述

我有一张这样的桌子:

// table
+----+--------+------------+
| id |  name  | reputation |
+----+--------+------------+
| 1  | jack   | 534431     |
| 2  | peter  | 334        |
| 3  | amos   | 1300       |
| 4  | carter | 13490      |
| 5  | basil  | 1351       |
+----+--------+------------+

现在我想要这个输出:

// newtable
+----+--------+------------+
| id |  name  | reputation |
+----+--------+------------+
| 1  | jack   | 534k       |
| 2  | peter  | 334        |
| 3  | amos   | 1.3k       |
| 4  | carter | 13.4k      |
| 5  | basil  | 1.3k       |
+----+--------+------------+

<小时>

嗯，首先，我想知道，我可以使用 MySQL 做到这一点吗?像这样:

---

Well, first of all, I want to know, Is it possible to I do that using MySQL? Something like this:

select id, name,
  concat(substr(reputation, 1, 4), IF(LENGTH(reputation) > 4, 'k', '')) as NewRep 
from table

我知道上面的查询是不正确的，我只是把它说成一个线索..！

I know the above query is not correct, I just said it as a clue ..!

但是如果使用 MySQL 无法实现这一点，那么我该如何使用 PHP 来实现呢?

But if implementing that is not possible using MySQL, then how can I do that using PHP?

if (strlen($result['reputation']) >= 4){
    $NewRep = substr($result['reputation'],0,3);
    $NewRep = round($NewRep).'k';
}

然而，这个 ^ 解决方案是不完整的.因为它不支持.5(point half)，而且它的sbust()也不支持.

However this ^ solution is incomplete. Because it does not support .5 (point half), and also its sbust() does not work as well.

推荐答案

SELECT 
    id,
    name,
    IF(reputation >= 1000,
        CONCAT(IF(LENGTH(LEFT(CAST(reputation / 100 AS CHAR), LENGTH(reputation)-2)) >= 4,
                    LEFT(reputation, LENGTH(reputation)-3),
                    LEFT(CAST(reputation / 1000 AS CHAR), LENGTH(reputation)-1)),
                'k'),
        reputation) AS reputation
FROM
    table

这样的事情可能会做到.

Something like that would probably do it.

输出:

+----+--------+------------+
| id |  name  | reputation |
+----+--------+------------+
| 1  | jack   | 534k       |
| 2  | peter  | 334        |
| 3  | amos   | 1.3k       |
| 4  | carter | 13.4k      |
| 5  | basil  | 1.3k       |
+----+--------+------------+

这篇关于如何显示每 K 的大数字(代表公斤)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！