需求：两个数据库中有两个不相关的表，我需要将它们合并，并在order2中新增不存在的product_name字段，并赋予默认值 先筛选要的的值，再合并 ``` $product_name = '默认值'； $news = DB::table("guild_data_log.user_login_202206") ->where('device_id', '0001592f6d71d20cad65bcc44c3497af') ->selectRaw('product_name, login_time, device_id') ->orderBy('login_time', 'desc'); $res = Db::table('guild_data_log.user_login_202207') ->where('device_id', 'aac87e997ac499712bbf2411202f2236') ->selectRaw('IFNULL(null, ?) as product_name, login_time, device_id', [$product_name]) ->orderBy('login_time', 'desc') ->union($news) ->paginate(10); ``` 大致原生sql ``` (select login_time, device_id from `guild_data_log`.`user_login_202207` where `device_id` = '01xx' order by `login_time` desc) union (select login_time, device_id from `guild_data_log`.`user_login_202206` where `device_id` = 'c8xx' order by `login_time` desc) limit 10 offset 0 ``` 先合并，再筛选要的值 ``` // 获取原生sql public function getBindingsSql($modObj) { $bindings = $modObj->getBindings(); $sql = str_replace('?', "'%s'", $modObj->toSql()); $sql = sprintf($sql, ...$bindings); return $sql; } public function test(){ $table = DB::table('guild_data_log.user_login_202206 as ul') ->selectRaw($selectRaw) ->where('device_id','0001592f6d71d20cad65bcc44c3497af') ->orWhere('device_id', 'aac87e997ac499712bbf2411202f2236'); // unionAllg不去重 $table->unionAll(DB::table('guild_data_log.user_login_202207 as ul') ->selectRaw($selectRaw) ->where('device_id','0001592f6d71d20cad65bcc44c3497af') ->orWhere('device_id', 'aac87e997ac499712bbf2411202f2236') ); $todayDataDetails = DB::table(DB::raw('(' . $this->getBindingsSql($table) . ') as ul')) ->selectRaw('max(ul.login_time) as last_login_time, ul.device_id') ->groupByRaw('ul.device_id') ->get(); } ``` 原生sql ``` select max(ul.login_time) as last_login_time, ul.device_id from ( (select ul.login_time, ul.device_id from `guild_data_log`.`user_login_202206` as `ul` where `device_id` = '0001592f6d71d20cad65bcc44c3497af' or `device_id` = 'aac87e997ac499712bbf2411202f2236') union all (select ul.login_time, ul.device_id from `guild_data_log`.`user_login_202207` as `ul` where `device_id` = '0001592f6d71d20cad65bcc44c3497af' or `device_id` = 'aac87e997ac499712bbf2411202f2236') ) as ul GROUP BY device_id ``` ps：记得两个表的字段要一一对应，不可以一边多一边少，也不可以换位置