ThinkPHP 5 根据账户类型的不同统计数据中使用CASE WHEN THEN SQL语句实例

项目场景

是外卖代运营平台，每个客户都有相关运营人员、签约业务员，分别在客户信息表里有user_id、business_id字段记录。

需求描述

在网站用户列表里，能直观看到实际每个人所关联的客户总数量、不同状态的数量分别是多少。

解决方案

$res_arr = User::alias('a')
            ->field('
            a.*,
            l.name as level_name,
            (SELECT COUNT(*) AS tp_count FROM `qqxz_shop_customer` where a.id=user_id and `delete_time` is  null) shop_sum,
            (SELECT COUNT(*) AS tp_count FROM `qqxz_shop_customer` where a.id=business_id and `delete_time` is  null) business_sum,
            (SELECT COUNT(*) AS tp_count FROM `qqxz_shop_customer` where a.id=(CASE WHEN user_type = 2 THEN user_id ELSE business_id END )  and `delete_time` is  null and operation_status=4) contract_sum,
            (SELECT COUNT(*) AS tp_count FROM `qqxz_shop_customer` where a.id=(CASE WHEN user_type = 2 THEN user_id ELSE business_id END )  and `delete_time` is  null and operation_status=3) stop_sum,    
            (SELECT COUNT(*) AS tp_count FROM `qqxz_shop_customer` where a.id=(CASE WHEN user_type = 2 THEN user_id ELSE business_id END )  and `delete_time` is  null and operation_status=1) ing_sum,
            (SELECT COUNT(*) AS tp_count FROM `qqxz_shop_customer` where a.id=(CASE WHEN user_type = 2 THEN user_id ELSE business_id END )  and `delete_time` is  null and operation_status=2) wait_sum
            ')
            ->join('qqxz_user_level l','l.id = a.level','left')
            ->where('a.user_type!=1')
            ->where('a.delete_time',null)
            ->order('a.create_time desc')
            ->where($sql)
            ->paginate($limit, $simple = false,['page'=>$page])
            ->toArray();

user_type 2是运营人员