我有一张记录各种文件大小的日志表,以字节为单位存储。我希望能够查询数据库并返回已转换为MB、GB等的最小可能浮点数。目前我可以返回MB中的值,但是如何继续分割到最小值并附加单位呢?
SELECT CONCAT( ROUND( SUM( data_transferred ) /1048576, 2 ) , ' MB' )
FROM `logs`
任何帮助将不胜感激。
更新:
根据voodoo417提供的链接,我更新了我的查询语句如下,它将输出最相关的文件大小到两个小数位,并附加单位(1000字节,1千字节,500兆字节,2千兆字节,等等):
SET @bytes := (SELECT SUM(data_transferred) FROM wp_ddownload_statistics);
SELECT
CASE
WHEN ABS(@bytes) < 1024 THEN CONCAT( ROUND( @bytes, 2 ), ' Bytes')
WHEN ABS(@bytes) < 1048576 THEN CONCAT( ROUND( (@bytes/1024), 2 ), ' KB')
WHEN ABS(@bytes) < 1073741824 THEN CONCAT( ROUND( (@bytes/1048576), 2 ), ' MB')
WHEN ABS(@bytes) < 1099511627776 THEN CONCAT( ROUND( (@bytes/1073741824), 2 ), ' GB' )
WHEN ABS(@bytes) < 1125899906842624 THEN CONCAT( ROUND( (@bytes/1099511627776), 2 ), ' TB')
WHEN ABS(@bytes) < 1152921504606846976 THEN CONCAT( ROUND( (@bytes/1125899906842624), 2 ), ' PB' )
WHEN ABS(@bytes) < 1180591620717411303424 THEN CONCAT( ROUND( (@bytes/1152921504606846976) ,2), ' EB' )
WHEN ABS(@bytes) < 1208925819614629174706176 THEN CONCAT( ROUND( (@bytes/1180591620717411303424), 2), ' ZB' )
WHEN ABS(@bytes) < 1237940039285380274899124224 THEN CONCAT( ROUND( (@bytes/1208925819614629174706176), 2), ' YB' )
WHEN ABS(@bytes) < 1267650600228229401496703205376 THEN CONCAT( ROUND( (@bytes/1237940039285380274899124224), 2), ' BB' )
END