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

 

posted @   安详的苦丁茶  阅读(401)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· 25岁的心里话
· ollama系列01:轻松3步本地部署deepseek,普通电脑可用
· 按钮权限的设计及实现

阅读目录(Content)

此页目录为空

点击右上角即可分享
微信分享提示