lavarel7学习笔记：聚合查询大全

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();