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'