MYSQL中有关SUM字段按条件统计IF函数case的使用

1.表结构

CREATE TABLE `test` (
  `id` varchar(64) NOT NULL,
  `name` varchar(64) NOT NULL,
  `state` varchar(64) DEFAULT '01',
  `num` varchar(64) DEFAULT NULL,
  `price` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2.sql

SELECT  SUM(price*IF(num>1,num,1)) AS a FROM test WHERE state='01' ;

3.效果

4.mysql同一个分组条件，不同查询条件分开统计

适用于统计多个查询条件
例如：查询总金额，线上金额、线下金额

SELECT SUM(t3.fee) AS fee,SUM(IF(t3.payway IN (0,3),t3.fee,0)) AS sum1,SUM(IF(t3.payway IN (1,2,4),t3.fee,0)) AS sum2 FROM t_busi_waybill t1
LEFT JOIN t_busi_order_waybill t2 ON t1.ID = t2.billid
LEFT JOIN t_busi_order t3 ON t2.orderid = t3.ID
WHERE t1.status = '5'