mysql查询SKU在最近创建的采购单中的到达时间、数量

1、需求

查询SKU在最近创建的采购单中的到达时间、数量

purchase_no表 purchase_no, create_at, arrival_at
purchase_detail表 purchase_no, goods_sn，quantity
查询SKU create_at最近的采购单的arrival_at，quantity

2、sql

SELECT
    pd.goods_sn,
    pn.arrival_at,
    pd.quantity
FROM
    purchase_detail pd
JOIN
    purchase_no pn ON pd.purchase_no = pn.purchase_no
JOIN
    (
        SELECT
            goods_sn,
            MAX(create_at) AS latest_create_at
        FROM
            purchase_detail pd
        JOIN
            purchase_no pn ON pd.purchase_no = pn.purchase_no
        GROUP BY
            goods_sn
    ) latest_pd ON pd.goods_sn = latest_pd.goods_sn
    AND pn.create_at = latest_pd.latest_create_at;

3、laravel写法

$innerQuery = DB::table('purchase_detail as pd')
    ->join('purchase_no as pn', 'pd.purchase_no', '=', 'pn.purchase_no')
    ->select('pd.goods_sn', DB::raw('MAX(pn.create_at) as latest_create_at'))
    ->groupBy('pd.goods_sn');

$latestPurchases = DB::table(DB::raw("({$innerQuery->toSql()}) as latest_pd"))
    ->mergeBindings($innerQuery)
    ->join('purchase_detail as pd', function ($join) {
        $join->on('pd.goods_sn', '=', 'latest_pd.goods_sn');
    })
    ->join('purchase_no as pn', function ($join) {
        $join->on('pd.purchase_no', '=', 'pn.purchase_no')
             ->on('pn.create_at', '=', 'latest_pd.latest_create_at');
    })
    ->select('pd.goods_sn', 'pn.arrival_at', 'pd.quantity')
    ->get();