mysql 两张不同结构的表连表查询，合并，并分页，排序 教你如
MySQL用union把两张没有关联的表合并，并使用条件查询排序分页
场景应用：
类似某团的搜索，既可以搜索店铺，也可以搜索商品；
类似某名片的搜索，既可以搜索企业，也可以搜索名片；
实现：
我用下面在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);
什么 ？上面的看不懂？好吧，我简单列下：
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
温馨提示：
坑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
