thinkphp 多表连接子查询 group by 分组以最新的一条数据为组

admin⁠

已于 2023-12-07 13:54:13 修改

阅读量569

点赞数

文章标签：数据库 php thinkphp

于 2023-11-30 16:23:08 首次发布

本文链接：https://blog.csdn.net/qq_19598963/article/details/134715637

版权

用这个

        $subQuery = Db::name('wms_orderitems')
            ->distinct(true)
            ->field('kw_id,orders_id,product_id')
            ->order('items_id desc')
            ->buildSql();


        $list = Db::name('wms_orders')->alias('order')
            ->join($subQuery.' item','item.orders_id=order.orders_id')
            ->field('order.dd_date,item.kw_id,item.product_id')
            ->group('item.kw_id')
                ->order('item.kw_id')
//                ->fetchSql(true)
            ->select()->toArray();

在这里插入图片描述

下面的可以参考


        $subQuery = Db::name('purchase_order_line')
            ->distinct(true)
            ->order('purchase_order_line_id desc')
            ->buildSql();

        $re = Db::name('purchase_order')
            ->alias('a')
            ->field([
                'a.company_id',
                'a.purchase_order_id',
                'b.price',
                'b.unit_cost',
                'b.product_id',
                'b.purchase_order_line_id',
                'ifnull(sum(ff.purchase_quantity),0) as ruku_sum',
                'ifnull(sum(f.purchase_quantity),0) as tuihuo_sum'
            ])
            ->join($subQuery.' b','a.purchase_order_id=b.purchase_order_id')
            ->whereTime('a.create_date','>',strtotime('-15 day'))
//            ->where(['a.status'=>4,'a.order_type_id'=>1])
            ->where('a.order_type_id', 1)
            ->where(function ($query) {
                $query->where('a.status', 4);
//                    ->whereOr('a.is_wms', 1);
            })

            ->leftjoin('cd_purchase_order_line_ruku ff','ff.purchase_order_id >= b.purchase_order_id and ff.price=b.price and b.product_id=ff.product_id and ff.status=4 and ff.order_type_id=1 and ff.company_id=a.company_id')
            ->leftjoin('cd_purchase_order_line_tuihuo f','f.product_id=b.product_id and f.purchase_order_id > a.purchase_order_id  and b.price=f.price  and f.status=7 and f.order_type_id=2 and f.company_id=a.company_id')
            ->having('ruku_sum > tuihuo_sum')
            ->group('b.product_id,a.company_id')
            ->select();