mysql条件统计的几种方式
1.SELECT
SELECT
(SELECT COUNT(1) FROM `staff_info` WHERE `user_type` =0 ) AS `a`,
(SELECT COUNT(1) FROM `staff_info` WHERE `user_type`=3 ) AS `b`
2.IF
select
count(IF(si.user_type =0, 1 ,NULL)) as managerNum,
count(IF(si.user_type =3, 1 ,NULL)) as vicePresidentNum,
count(IF(si.user_type =2, 1 ,NULL)) as teamLeaderNum,
count(IF(si.user_type =1, 1 ,NULL)) as partnerNum from staff_info as si
3.CASE WHEN
SELECT
COUNT(CASE WHEN si.user_type = 0 THEN 1 ELSE NULL END) As managerNum,
COUNT(CASE WHEN si.user_type = 3 THEN 1 ELSE NULL END) AS vicePresidentNum,
COUNT(CASE WHEN si.user_type = 2 THEN 1 ELSE NULL END) AS teamLeaderNum
FROM staff_info si
4.统计某状态下的订单总金额
SELECT
SUM( IF(amount ,amount ,0) ) AS 订单总金额,
SUM( CASE WHEN `status` = '0' THEN IFNULL(`amount`,0) ELSE 0 END) AS 订单总金额,
FROM orders