使用CI查询构建器创建此SQL的困难在于左联接内的select部分.您可以使用 join()进行构建函数将$ table参数替换为 SELECT 部分:

The difficulty to create this SQL with CI query builder lies in the select part inside the left join. You can build it using the join() function replacing the $table parameter with the SELECT part:

join($ table，$ cond [，$ type =''[，$ escape = NULL]])参数:

join($table, $cond[, $type = ''[, $escape = NULL]]) Parameters:

    $table (string) – Table name to join
    $cond (string) – The JOIN ON condition
    $type (string) – The JOIN type
    $escape (bool) – Whether to escape values and identifiers

这是最终的CI代码:

$q=$this->db1   ->select ('*')
                ->join('(
                                select `product_id`
                                ,count(*) 
                                from `sma_sale_items`
                                group by `product_id`
                            ) s','p.id = s.product_id','left')
                ->order_by('count(*)', 'DESC')
                ->get('sma_products p');

return $q->result();