Thinkphp中复杂SQL的写法

1. SQL 多表联接的写法：

$spc_common_default = D("spc_common_default");
$filter['S.spc_common_id'] = array('eq',71);
$model_list  = $spc_common_default->table('__SPC_COMMON_DEFAULT__ S')
    ->join('LEFT JOIN __SPC_PGROUP_DEFAULT__ U ON S.spc_common_id = U.spc_common_id')
    ->field('S.*')
    ->where($map)
    ->select();

以上代码相当于以下SQL语句：

SELECT S.* FROM ev_spc_common_default as S
LEFT JOIN ev_spc_group_default as U
ON S.spc_common_id=U.spc_common_id
WHERE s.spc_common_id=71

2. SQL Group 写法:

$model_list = $this->table('__ALBUM_CLASS__ A')->join('LEFT JOIN __ALBUM_PIC__ B ON A.aclass_id=B.aclass_id')
    ->field('A.aclass_id,A.app_id,A.aclass_name,A.aclass_des,A.aclass_conver,A.aclass_dic,A.is_default,A.create_time,count(B.aclass_id) as ClassCount')
    ->where($condition)
    ->group('A.aclass_id')
    ->order('A.create_time')
    ->select();

3. 统计查询(count,sum...)

$model->table('__ALBUM_CLASS__')->where($condition)->count();
$model->table('__ALBUM_CLASS__')->count('columnName');
$sumScore = $User->sum('score');