laravel orwhere union all 查询统计分页sql語句

union all查询统计总数量

select sum(a.b) as num from (
select count( ) as b from table_1 
union all 
select count( ) as b from table_2
) as a（注意这里要取个别名）

分表后多表,UNION 和UNION ALL查询分页

1. UNION ALL和 UNION 的区别

UNION ALL理论上要比 UNION的查询效率更高一些，因为 UNION会将结果集中的数据进行过滤，将相同的数据进行剔除后返回，而 UNION ALL不进行此操作，它会将结果集全部返回

2.UNION ALL分页要点

如果需要分页的时候只需要将他们联合查询的结果集当做一个表来查询就好了。

3.UNION ALL查询结果统计

SELECT COUNT(*) FROM((SELECT id,money,createtime,status FROM pre_pay_log WHERE memb_id = 51225) UNION ALL (SELECT id,money,createtime,status FROM pre_cash_log WHERE memb_id = 51225)) as a  

4、UNION ALL查询分页

SELECT * FROM((SELECT id,money,createtime,status,error AS form_account,payment_no AS type_name FROM pre_pay_log WHERE 1 AND memb_id =51225) UNION ALL (SELECT id,money,createtime,status,form_account,type_name FROM pre_cash_log WHERE 1 AND memb_id =51225)) as a ORDER BY a.id DESC LIMIT 0,20  

将合并查询后的语句派生出一个新的表后，我们就可以使用平常的分页操作进行分页了。

laravel多条件查询(and，or嵌套查询)

問題

select 
    * 
from 
    homework 
where 
    (id between 1 and 10 or id between 50 and 70) 
    and complete = 1 
    and (title like 'a%' or title like 'b%');

解答

$homeworks = Homework::where(function ($query) {
    $query->whereBetween('id', [1, 10])
          ->orWhereBetween('id', [50, 70]);
})->where('complete', 1)
->where(function ($query) {
    $query->where('title', 'like', 'a%')
          ->orWhere('title', 'like', 'b%');
})->get();

其他

$data = DB::table('group')
            ->join('group_users as gu','group.group_id','=','gu.group_id')
            ->where(['gu.user_id'=>$uid])
            ->where(function($query){
                $query->where('is_super',1)
                    ->orwhere(function($query){
                        $query->where('is_super',2);
                    });
            })
            ->orderBy('group.group_id', 'desc')
            ->select('group.group_id','group.group_name','group.type_id','group.logo')
            ->get();

$customer_info=DB::table('customer as c')
            ->leftJoin('customer_details as cd','c.customer_id','=','cd.customer_id')
            ->leftJoin('costomer_linkman as cl','c.customer_id','=','cl.customer_id')
            ->whereIn('c.user_id',$userIdarr)
            ->where($where_two)
            ->where(function($query){
                $query->where('cd.deal_status',0)
                    ->orwhere('cd.deal_status',null);
            })
            ->where('c.customer_name','like','%'.$customer_name.'%')
            ->select('c.customer_id','c.user_id','c.customer_name','cl.linkman_phone','cd.intention_level_id','c.add_time')
            ->paginate($limit);

select * from orders_history where id in
(select order_id from trade_2 where goods = 'pen')
limit 100;

select * from orders_history where type=8 and
id>=(select id from orders_history where type=8 limit 100000,1)
limit 100;

總結

1. 使用ORM查询数据可以得到model数据集，能更方便的处理数据。
2. laravel的where方法使用闭包可以有效的构建嵌套的where子句（在这里，使用where的闭包相当于在构建sql的时候加一个括号

laravel多条件查询(and，or嵌套查询)
Laravel 的 where or 查询 加底層代碼解釋
Laravel 系列：orWhere 条件式