如何获取在单表中把两个字段运算后统计行数据

以往的字段间统计,查询使用sum函数求出两个字段运算的结构,再赋予新的别名作为新列

SELECT SUM(`market_price` - `shop_price`) AS 'price_amount' FROM `tp_goods` GROUP BY `goods_id`;

然后统计单个字段列最大值是这样的

SELECT MAX(`price_amount`) FROM (xxx) b;

由于第一条查询语句查询出来的数据相当是新表;再由得出组合语句

SELECT MAX(`price_amount`) FROM (SELECT SUM(`market_price` - `shop_price`) AS 'price_amount' FROM `tp_goods` GROUP BY `goods_id`) b;
查询花费 0.0006 秒

为查询中内嵌查询
更为高级的写法,在大数据量中查询时间更短,下面执行时间花费 0.0004 秒

SELECT
  `a`.`market_price` - `a`.`shop_price` AS 'discount'
FROM
  `tp_goods` a
    INNER JOIN `tp_goods_zh` AS b ON `a`.`goods_id` = `b`.`goods_id`
    INNER JOIN `tp_auction` AS c ON `a`.`goods_id` = `c`.`goods_id`
WHERE
  `sale_type` = 5 AND `start_time` BETWEEN 1551196800 AND 1551283200
LIMIT 1

ThinkPHP版本多写法为

$goods
   ->alias('a')
   ->join("$lang AS b ON a.goods_id=b.goods_id")
   ->join('__AUCTION__ AS c ON a.goods_id = c.goods_id')
   ->where(array('sale_type' => 5, 'start_time' => array('between', array($today, $todayEnd))))
   ->field('a.market_price - a.shop_price as discount')
   ->find();

标签: nermif, mysql字段二次运算

仅有一条评论

  1. 您的博客已在博友大全收录,感谢您的提交~

添加新评论