Mysql根据前缀计算总内存

Question

有这个表:

 CREATE TABLE IF NOT EXISTS `assets` (
  `asset_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `asset_memory` varchar(255) NOT NULL,
  `asset_memory_prefix` tinyint(1) NOT NULL DEFAULT '2',
  PRIMARY KEY (`asset_id`),
  KEY `Locations` (`position_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

我需要计算特定 asset_id 的内存总和asset_memory 和 asset_memory_prefix 其中 asset_memory_prefix 定义如下:

    $_prefix = array(
    array("id" => 0, "prefix" => "B", "prefix_name" => "byte", "pow_value" => "0"),
    array("id" => 1, "prefix" => "Kb", "prefix_name" => "Kilobyte", "pow_value" => "1"),
    array("id" => 2, "prefix" => "Mb", "prefix_name" => "Megabyte", "pow_value" => "2"),
    array("id" => 3, "prefix" => "Gb", "prefix_name" => "Gigabyte", "pow_value" => "3"),
    array("id" => 4, "prefix" => "Tb", "prefix_name" => "Terabyte", "pow_value" => "4"),
    array("id" => 5, "prefix" => "Pb", "prefix_name" => "Petabyte", "pow_value" => "5"),
    array("id" => 6, "prefix" => "Eb", "prefix_name" => "Exabyte", "pow_value" => "6"),
    array("id" => 7, "prefix" => "Zb", "prefix_name" => "Zettabyte", "pow_value" => "7")
    );

所以条目可能是这样的:

    asset_id, asset_memory, asset_memory_prefix
    1, 1024, 2
    2, 1024, 3
    3, 2048, 2

如何在 SELECT 中执行此操作？

Answer 1

您只想要分组依据吗？

select asset_id, sum(asset_memory) as asset_memory, asset_memory_prefix
from assets
group by asset_id, asset_memory_prefix;

编辑:

我认为这就是您想要的，假设 asset_memory_prefix 是一个表:

select a.asset_id,
       sum(pow(1000, ap.pow_value) * asset_memory) as total_memory_bytes
from assets a join
     asset_prefix ap
     on a.asset_memory_prefix = ap.asset_memory_prefix
group by a.asset_id;

我不确定您想要 1,000 还是 1,024 (2^10)。

您应该能够直接使用asset_memory_prefix:

select a.asset_id,
       sum(pow(1024, ap.asset_memory_prefix) *
           asset_memory
          ) as total_memory_bytes
from assets a join
     asset_prefix ap
     on a.asset_memory_prefix = ap.asset_memory_prefix
group by a.asset_id;