mysql根据关联表查询统计字段进行排序

mysql根据关联表查询统计字段进行排序，需求是查询所有的主账号数据，然后根据子账号的数量进行按高到低排序，第一想法就是需要做order by那么就需要把统计子账号的结果作为一个字段，那这时候就可以使用子查询来实现了。

1、按照原生sql的写法：

SELECT id,num FROM aikehou_brand a 

LEFT JOIN (

        SELECT COUNT(*) AS num,pid FROM aikehou_brand WHERE pid <> 0 AND `status` <> - 1 GROUP BY pid

) AS b ON a.id = b.pid WHERE a.pid = 0 AND a.`status` <> - 1 

2、thinkPHP5.1的子查询写法

//查询条件

$where = [];

$where[] = ['a.pid', '=', 0];

$where[] = ['a.status', 'neq', -1];

//行业

if ($industry != 0) {

        $where[] = ['a.sys_tags', 'like', "%,".$industry.",%"];

}

//总数

$total = $this->model->alias('a')->where($where)->count('id');

//查询所有账号ID

$all_brand_ids = $this->model->alias('a')->where($where)->column('id');

//子查询

$sub_where = [];

$sub_where[] = ['pid', 'gt', 0];

$sub_where[] = ['status', 'neq', -1];

$subsql = $this->model->where($sub_where)->field('count(0) as num,pid')->group('pid')->buildSql();

//查询

$data = $this->model->alias('a')->field('a.id,a.pid,a.sponsor_id,a.username,a.brand,a.sys_tags, IFNULL(b.num, 0) as subacc_number')->leftJoin([$subsql=> 'b'], 'a.id = b.pid')->where($where)->page($page, $limit)->order('subacc_number desc, a.id desc')->select()->toArray();