登录

从实例讲解ThinkPHP6联表聚合查询

ThinkPHP
0 231

前些天有朋友问到了一个联表的聚合查询问题,想到此问题可能很多新手处理都比较棘手,现在特意分享出来。

我们有两个数据表:

bus表

:001

user表:

002

需求:统计每个人上车数量

如何实现呢?

第一步:联表

这种场景肯定需要两个数据表联表,我们先不考虑统计,我们先进行两个数据表联表。

  1. $data=Db::name('user')->alias('a')->join('bus b''a.user_id=b.user_id')->select()->toArray();

alias是别名,join是联表的数据表,并且有联表条件a.user_id=b.user_id,这样我们就能够获取到两个数据表联表的数据了。

003

第二步:聚合查询

在做聚合查询前,我们先看下官方手册的教程。

004

因为我们最终是要获取统计的数量,所以我们首先确定用count()方法,所以我们修改查询语句:

  1. $data=Db::name('user')->alias('a')->field('count(b.user_id) AS c'')->join('bus b''a.user_id=b.user_id')->select()->toArray();

这其中c,是别名。

我们需要根据user_id这个字段进行聚合查询,是根据这个字段统计的,所以我们肯定是group(user_id),也就是按照user_id这个字段进行分组。

我们继续修改查询语句:

  1. $data=Db::name('user')->alias('a')->field('count(b.user_id) AS c')->join('bus b''a.user_id=b.user_id')->group('a.user_id')->select()->toArray();

这样我们就实现了我们的最终查询结果。

第三点:注意情况

在上面的查询语句中,如果mysql是5.7版本,那么需要格外注意。比如在mysql5.7中在field中添加a.*,就会报错:

  1. $data=Db::name('user')->alias('a')->field('a.*count(b.user_id) AS c')->join('bus b''a.user_id=b.user_id')->group('a.user_id')->select()->toArray();
  2. [object object]

为什么会有这样的错误呢?

005

MYSQL5.7为了更好的性能,对sql_mode的限制。

ONLY_FULL_GROUP_BY: 对于GROUP BY聚合操作的时候,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中,这也就是报错所在。

我们可以修改mysql配置:

修改/etc/my.cnf,将sql_mode=中的only_full_group_by给删掉

这样一个联表的聚合查询就实现了,我们遇到这个问题的时候,不要心急,根据最终的需求,一步步的拆分一步步的实现。

联表(联合、关联、join)查询

Db::table('think_artist')

->alias('a')

->join('think_work w''a.id = w.artist_id')

->join('think_card c''a.card_id = c.id')

->select();

$join = [

    ['think_workw''a.id=w.artist_id']

    ['think_cardc''a.card_id=c.id']

];

Db::table('think_user')->alias('a')->join($join)->select();

获取用户数:

Db::table('think_user')->count();

实际生成的SQL语句是:

SELECT COUNT(*) AS tp_count FROM `think_user` LIMIT 1

或者根据字段统计:

Db::table('think_user')->count('id');

生成的SQL语句是:

SELECT COUNT(id) AS tp_count FROM `think_user` LIMIT 1

获取用户的最大积分:

Db::table('think_user')->max('score');

生成的SQL语句是:

SELECT MAX(score) AS tp_max FROM `think_user` LIMIT 1

如果你要获取的最大值不是一个数值,可以使用第二个参数关闭强制转换

Db::table('think_user')->max('name'false);

获取积分大于0的用户的最小积分:

Db::table('think_user')->where('score' '>' 0)->min('score');

和max方法一样,min也支持第二个参数用法

Db::table('think_user')->where('score' '>' 0)->min('name'false);

获取用户的平均积分:

Db::table('think_user')->avg('score');

生成的SQL语句是:

SELECT AVG(score) AS tp_avg FROM `think_user` LIMIT 1

统计用户的总成绩:

Db::table('think_user')->where('id'10)->sum('score');

生成的SQL语句是:

SELECT SUM(score) AS tp_sum FROM `think_user` LIMIT 1

如果你要使用group进行聚合查询,需要自己实现查询,例如:

Db::table('score')->field('user_idSUM(score) AS sum_score')->group('user_id')->select();

发表评论

0 个回复

1xBet

1xBet

242 2017-05-15 加入 天津

(有时候读书是一种巧妙地避开思考的方法)

热门推荐

友情连接