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