lavarel7聚合查询
1、查询总数
//select count(*)from articles where id>5
//select count(id)from articles where id>5
$res =DB::table(‘articles’)->count();
$res =DB::table(‘articles’)->where(‘id’,’<’,5)->count();
2、统计复合条件的记录的字段的和
// select sum(pv) from articles
$res = DB::table(‘articles’)->sum(‘pv’);
3、找出复合查询条件的记录的字段的最小值
// select min(pv) from articles
// select min(pv) from articles where status=1 and pv>=16
$res = DB::table(‘articles’)->where(‘status’,1)->where(‘pv’,’>=’,16)->min(‘pv’);
4、找出复合查询条件的记录的字段的最大值
// select max(pv) from articles
$res = DB::table(‘articles’)->max(‘pv’);
5、找出复合查询条件的记录的字段的平均值
// select avg(pv) from articles
$res = DB::table(‘articles’)->avg(‘pv’);
6、查询pv大于0小于23的记录
// select from articles where status=1 and pv>0 and pv<23
// select from articles where status=1 and pv between 0 and 23
$res = DB::table(‘articles’)->where(‘status’,1)->whereBetween(‘pv’,[0,23])->get()->all();
7、in 查询
// select from articles where pv=3 or pv=20 or pv=10
// select from articles where pv in(3,20,10)
$res = DB::table(‘articles’)->whereIn(‘pv’,[3,20,10])->get()->all();
8、or 查询(容易全表查询，尽量不使用)
// select * from articles where pv=3 or pv=20 or pv=10
$res = DB::table(‘articles’)->orWhere(‘pv’,3)->orWhere(‘pv’,20)->orWhere(‘pv’,10)->get()->all();
9、join连表查询
// select from articles,article_cate where articles.cate_id=article_cate.cid
$res = DB::table(‘articles’)->leftJoin(‘article_cate’,’articles.id’,’=’,’article_cate.cid’)->select(‘articles.‘,’article_cate.title as ctitle’)->get()->all();
