记录下我的一条很长的sql

一杯水，一支烟，一行代码写一天

public function viewForStatistic()
    {
   
        $bargainGoodsProductSql = (new ShopToolBargainGoodsProductMO())->alias('bgp')
            ->join((new ShopGoodsProductMO())->getTableName() . ' gp', 'bgp.product_no = gp.product_no', 'left')
            ->where('bgp.delete_time', '=', 0)
            ->group('gp.goods_no')
            ->field(Db::raw('gp.goods_no, bgp.product_no, min(bgp.price) as price, gp.product_price'))
            ->buildSql();

        $goodsSql = (new ShopGoodsMO())->alias(' sg')
            ->where('delete_time', '=', 0)
            ->join($bargainGoodsProductSql . ' sgp', 'sg.goods_no = sgp.goods_no')
            ->field('sg.goods_no,sg.goods_title,sg.goods_cover,sg.goods_price,sgp.price,sgp.product_price')
            ->buildSql();

        $countSql = (new ShopToolBargainApplyMO())
            ->group('bargain_no')
            ->field(Db::raw('bargain_no, count(*) as apply_count,count( if ( exchange_status =' . ShopToolBargainApplyConstant::EXCHANGE_STATUS_HAS_EXCHANGE . ',id,null)) as receive_count,count( if ( status =' . ShopToolBargainApplyConstant::STATUS_SUCCESS . ',id,null)) as success_count,count(distinct student_no) as apply_user_count, count( distinct if ( status =' . ShopToolBargainApplyConstant::</