时间:2021-05-25
本项目中关联了2个数据库
'default' => env('DB_CONNECTION', 'mysql'), //默认使用mysql为连接库'connections' => [ 'mysql' => [ 'driver' => 'mysql', 'host' => '192.168.0.xx', 'database' => 'database', 'username' => 'root', 'password' => '', 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => 'tb_', 'strict' => false, ], 'mysql_snapshot' => [ 'driver' => 'mysql', 'host' => env('DB_HOST_SNAPSHOT', '192.168.0.xx'), 'database' => env('DB_DATABASE_SNAPSHOT', 'snapshot'), 'username' => env('DB_USERNAME_SNAPSHOT', 'root'), 'password' => env('DB_PASSWORD_SNAPSHOT', ''), 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => 'tb_', 'strict' => false, ], ],在某个需求中,需要使用子查询获取snapshot快照表库的关联数据,从而实现以下sql逻辑
SELECT ... From (SELECT sum(game_count) AS sum_count, max(game_count) AS max_count, game_room_id, record_date FROM `tb_xx_snapshot` WHERE record_date BETWEEN '2017-05-17' AND '2017-05-23' AND type = '1' GROUP BY game_room_id) as mainINNER JOIN `tb_xx_snapshot` AS `tb_gg` ON tb_gg.game_count = main.max_count and tb_gg.game_room_id = main.game_room_idwhere tb_gg.record_date BETWEEN '2017-05-17' AND '2017-05-23'AND tb_gg.type = '1'GROUP BY tb_gg.game_room_id;其中子查询主要用到以下query builder语句
$query = DB::table('xx_snapshot')->where('xx','yy')->groupBy('xx');$main = DB::connection('mysql_snapshot') ->table(DB::raw("({$query->toSql()}) as tb_main")) ->mergeBindings($query->getQuery()) // 绑定参数,否则sql语句会只有'?' ->get();而join语句中可传入匿名函数重新构造,如再其中加多几个连接条件,或者查询条件
$con = DB::table('xx_snapshot') ->join('xx_snapshot as gg', function ($query) { $query->on('gg.game_count', '=', 'xx_snapshot.max_count') ->on('gg.game_room_id', '=', 'xx_snapshot.game_room_id') ->where('gg.xx','123'; })实现上述需求完整代码如下:
$subQuery= GameroomModel::select(DB::raw('sum(game_count) as sum_count,max(game_count) as max_count,record_date,game_room_id')) ->whereBetween('record_date',[$beginDay,$endDay]) ->where('type','1') ->groupBy('game_room_id');$main = DB::connection('mysql_snapshot') ->table(DB::raw("({$subQuery->toSql()}) as tb_main")) ->mergeBindings($playerGame->getQuery()) ->join('gameroom_snapshot as gg', function ($join) { $join->on('gg.game_count', '=', 'main.max_count') ->on('gg.game_room_id', '=', 'main.game_room_id'); }) - >select('main.max_count','main.sum_count','gg.record_date','main.game_room_id') ->whereBetween('gg.record_date',[$beginDay,$endDay]) ->groupBy('main.game_room_id') ->get();代码中子查询和外层都group by了一次,应该可以再优化一下.
以上这篇关于laravel 子查询 & join的使用就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
使用连接(JOIN)来代替子查询(Sub-Queries)MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果,然
做分页查询:1.对于mysql,不推荐使用子查询和join是因为本身join的效率就是硬伤,一旦数据量很大效率就很难保证,强烈推荐分别根据索引单表取数据,然后在
关于阻塞主线程join的错误用法Thread.join()作用为阻塞主线程,即在子线程未返回的时候,主线程等待其返回然后再继续执行.join不能与start在循
ThinkPHP中关联查询(即多表联合查询)可以使用table()方法或和join方法,具体使用如下例所示:1、原生查询示例:$Model=newModel()
zend框架的查询器join()链表使用示例复制代码代码如下: