thinkphp 3.2 多表查询 group-优快云博客

本文分析了使用PHP框架进行复杂SQL查询的方法，包括多表连接、字段筛选、分组及排序等高级技巧，并提供了多个示例对比不同的写法及其效果。

分析一

$res = M('member')
                    ->table('__MEMBER__ as a')
                    ->join('__ORDER__ as b')
                    ->field('a.id,b.order_sn,count(b.id) as total')
                    ->where('b.receive_member_id = a.id')
                    ->group('a.id')
                    ->order('total desc')
                    ->limit($page->firstRow, $page->listRows)
                    ->select();

1. $res = M('member') ,$res = M('member a') ,$res = M('member as a') 意思相同

2.$res = M('member')->table('__MEMBER a') , $res = M('member')->table('__MEMBER as a') 相同

分析二

$res = M('member_attest')
                ->table('__MEMBER_ATTEST__ a')
                ->join('__MEMBER__ b')
                ->join('__ORDER__ c')
                ->field('a.id,a.member_id,a.real_name,a.rank,b.mobile,count(c.id) as total')
                ->where('c.receive_member_id = b.id and  b.id = a.member_id')->group('a.id')
                ->order('total desc')
                ->limit($page->firstRow, $page->listRows)
                ->select();

和

$res = M('member_attest')
                ->table('__MEMBER_ATTEST__ a')
                ->join('__MEMBER__ b ON b.id = a.member_id')
                ->join('__ORDER__ c')
                ->field('a.id,a.member_id,a.real_name,a.rank,b.mobile,count(c.id) as total')
                ->where('c.receive_member_id = b.id')->group('a.id')
                ->order('total desc')
                ->limit($page->firstRow, $page->listRows)
                ->select();

效果一样，join里面也可以不用带 on a.xx=b.xxx ,查询条件可以全部放到where里面

分析三

$res = M('member_attest')
                    ->table('__MEMBER_ATTEST__ a,__MEMBER__ b')
                    ->where('a.member_id = b.id')
                    ->select();

$res = M('member_attest a')
                ->join('__MEMBER__ b')
                ->where('a.member_id = b.id')
                ->select();

$res = M('member_attest a')
                ->join('__MEMBER__ b ON a.member_id = b.id')
                ->select();

这三个都可以正常使用

转载于:https://www.cnblogs.com/wesky/p/6425486.html