mysql 两张不同结构的表连表查询，合并，并分页，排序教你如何实现UNION · php 经验秘籍 php 入门 php秘籍 php5年经验秘籍

# MySQL用union把两张没有关联的表合并，并使用条件查询排序分页补充：（ps：调头回来研究研究，补充） 1，简单sql语句，先把2张表集合在一起，你发现news表数据在上面，接着才到card, 有没有发现card 的name字段没有，只显示 title 字段所以以前面的数据和字段优先 SELECT id,title,created_at FROM news UNION SELECT id,`name`,created_at FROM card ; ![](https://img.kancloud.cn/f4/24/f4244b57c3923a5d2e9314aa38010508_904x488.png) 2，进一步优化，第一步看到2张表数据都在里面区分不出来是哪张表，所以改了下sql语句: SELECT id,title,created_at ,0 as type FROM news UNION SELECT id,`name`,created_at ,1 as type FROM card ; ![](https://img.kancloud.cn/f4/58/f4584bbc92f84057ab72f1ba506aedff_931x519.png) 3,进一步优化，增加了个排序功能，如下： ![](https://img.kancloud.cn/57/35/57356eefaa062bf4f273a771ce0fbbe0_1186x530.png) ### 场景应用：类似某团的搜索，既可以搜索店铺，也可以搜索商品；类似某名片的搜索，既可以搜索企业，也可以搜索名片； ### 实现：我用下面在laravel中实现的代码案例说下： $test1= UserHistoryCompany::orWhere(function ($query) use($title) { $query->where('user_history_company.user_id', '=', $this->user_id)->where('is_delete',0) ->where('company.name', 'like', '%'.$title.'%'); ->join('company','company.id','=','user_history_company.company_id') ->select('user_history_company.id','user_history_company.user_id','user_history_company.company_id as c_id', 'user_history_company.updated_at','company.name as company_name','company.id as company_id','logo', DB::raw('2 as type , 0 as card_id , 0 as head_img , 0 as job_name , 0 as department_name , 0 as name ')); $result= UserHistoryCard::orWhere(function ($query) use($title) { $query->where('user_history_card.user_id', '=', $this->user_id)->where('is_delete',0) ->where('company.name', 'like', '%'.$title.'%'); ->orWhere(function ($query) use($title) { $query->where('user_history_card.user_id', '=', $this->user_id)->where('is_delete',0) ->where('card.name', 'like', '%'.$title.'%'); ->orWhere(function ($query) use($title) { $query->where('user_history_card.user_id', '=', $this->user_id)->where('is_delete',0) ->where('card.mobile', 'like', '%'.$title.'%'); ->join('card','card.id','=','user_history_card.card_id') ->join('company','company.id','=','card.company_id') ->select('user_history_card.id','user_history_card.user_id','user_history_card.card_id as c_id', 'user_history_card.updated_at','company.name as company_name','company.id as company_id','logo', DB::raw('1 as type , user_history_card.card_id , card.head_img , card.job_name , card.department_name , card.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); //上面 xxxxxxxxxx->unionAll($test1); 其实发现也可以直接 ->unionAll($test1 ->orderBy('updated_at', 'desc') ->paginate(request()->input('page_num') ?? 50); 然后下面的 $sql = $result->toSql();$result = DB::table(DB::raw("(.........操作都不需要了，什么？上面的看不懂？好吧，我简单列下： 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 条件2 2、当两个表的属性不同时，要分别查询每个属性，给不同属性名取一个别名,例如收入和支出表:(表中的id和remark是相同属性名，其他的属性名都不同) select * from( (select id, a1 as a, b1 as b, c1 as c, d1 as d, e1 as e, updated_at from a1_table) union all (select id, a2 as a, b2 as b, c2 as c, d2 as d, e2 as e, updated_at from a2_table) ) as c ![](https://img.kancloud.cn/70/6e/706e4acb3e3340f40bdaebb32e6e6336_875x693.png) 温馨提示：坑1：虽然2个表结构要整合再一起排序分页，就要通过 as 别名来转换相同的字段，不然就被前面一个查询条件的字段给覆盖了，坑2：上面的a1,b1,c1 顺序要对着a2,b2,c2，才行，否则就被前面一个查询条件的字段给覆盖了，错误示范： 1，a1,b1,c1 顺序要对着c2,b2,a2，你查询出来的a1值就会到c2里面，c1到a2里面，不信你可以试试。坑3：2个查询字段数量必须一致，否则就会报错。完事了，比较懒，想看详情的话，看下面2个链接，前人写的 ———————————————— 参考链接：https://blog.csdn.net/qq_43341807/article/details/120115151 参考链接：https://www.cnblogs.com/zhengchuzhou/p/10262260.html