2023年6月23日发(作者：)

mysql两张不同结构的表连表查询，合并，并分页，排序教你如何实现UNION MySQL⽤union把两张没有关联的表合并，并使⽤条件查询排序分页 场景应⽤：类似某团的搜索，既可以搜索店铺，也可以搜索商品；类似某名⽚的搜索，既可以搜索企业，也可以搜索名⽚； 实现：我⽤下⾯在laravel中实现的代码案例说下：$test1= UserHistoryCompany::orWhere(function ($query) use($title) { $query->where('user_history__id', '=', $this->user_id)->where('is_delete',0) ->where('', 'like', '%'.$title.'%'); }) ->join('company','','=','user_history_y_id') ->select('user_history_','user_history__id','user_history_y_id asc_id', 'user_history_d_at',' as company_name',' ascompany_id','logo', DB::raw('2 as type , 0 as card_id , 0 as head_img , 0 as job_name , 0 as department_name , 0 asname ')); $result= UserHistoryCard::orWhere(function ($query) use($title) { $query->where('user_history__id', '=', $this->user_id)->where('is_delete',0) ->where('', 'like', '%'.$title.'%'); }) ->orWhere(function ($query) use($title) { $query->where('user_history__id', '=', $this->user_id)->where('is_delete',0) ->where('', 'like', '%'.$title.'%'); }) ->orWhere(function ($query) use($title) { $query->where('user_history__id', '=', $this->user_id)->where('is_delete',0) ->where('', 'like', '%'.$title.'%'); }) ->join('card','','=','user_history__id') ->join('company','','=','y_id') ->select('user_history_','user_history__id','user_history__id as c_id', 'user_history_d_at',' as company_name',' ascompany_id','logo', DB::raw('1 as type , user_history__id , _img , _name ,ment_name , ')) ->unionAll($test1); $sql = $result->toSql(); $result = DB::table(DB::raw("($sql) as a ")) ->mergeBindings($result->getQuery()) ->orderBy('updated_at', 'desc') ->paginate(request()->input('page_num') ?? 50); dd($result);什么 ？上⾯的看不懂？好吧，我简单列下：1.当两张表属性完全相同时，可以直接合并(union会删除重复数据，union all会返回所有结果集）（1）将两个表合并 select * from 表1 union select * from 表2 select * from 表1 union all select * from 表2（2）将两个表合并,并使⽤条件查询 select * from ( select * from 表1 union select * from 表2) as 新表名 where 条件1 and 条件22、当两个表的属性不同时，要分别查询每个属性，给不同属性名取⼀个别名,例如收⼊和⽀出表:(表中的id和remark是相同属性名，其他的属性名都不同)select * from((select id,a1 as a,b1 as b,c1 as c,d1 as d,e1 as e,updated_atfrom a1_table)union all(select id,a2 as a,b2 as b,c2 as c,d2 as d,e2 as e,updated_atfrom a2_table)) as c温馨提⽰：坑1：虽然2个表结构要整合再⼀起排序分页，就要通过 as 别名来转换相同的字段，不然就被最后⼀个查询条件的字段给覆盖了，坑2：上⾯的a1,b1,c1 顺序要对着a2,b2,c2，才⾏，否则就被最后⼀个查询条件的字段给覆盖了，错误⽰范：1，a1,b1,c1 顺序要对着c2,b2,a2，你查询出来的a1值就会到c2⾥⾯，c1到a2⾥⾯，不信你可以试试。坑3：2个查询字段数量必须⼀致，否则就会报错。完事了，⽐较懒，想看详情的话，看下⾯2个链接，前⼈写的————————————————