sql - 小数点前的数字为0时如何删除小数位

Question

我正在尝试从某些值中删除小数位

例如，我当前的代码是

select po_id, AVG(price*quantity) * count(*) AS 'Total Cost'
from po_items natural join items 
group by po_id; 

这是输出即时消息

po_id       Total Cost
----------  ----------
AAA         2217.5
BBB         255.0
CCC         5787.5
DDD         10000.0
GGG         1000.0

我试图获得的输出是

po_id       Total Cost
----------  ----------
AAA         2217.5
BBB         255
CCC         5787.5
DDD         10000
GGG         1000

Answer 1

确定，如下所示：

sqlite> select cost from my_table;
2217.5
255.0
5787.5
10000.0
1000.0

sqlite> select replace(cast(cost as string),'.0','') from my_table;
2217.5
255
5787.5
10000
1000

您完整的SQL可能是：

select 
    po_id, 
    replace(cast(avg(price*quantity)*count(1) as string),'.0','') as 'Total Cost'
from 
    po_items
join 
    items 
group by 
    po_id;