技术饭

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

copylian    0 评论    342 浏览    2021.10.29

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();

飓风呀
感谢你的支持,我会继续努力!
扫码打赏,感谢您的支持!

文明上网理性发言!

  • 还没有评论,沙发等你来抢