MYSQL 统计时间段内的销售情况

为了统计商品在特定时间段内的销售情况，我们需要从销售记录表中提取数据，并可能需要关联商品表以获取商品的详细信息。假设我们有两个表：
base_sales：销售记录表
base_goods：商品表
表结构示例
base_sales 表结构：
id (销售记录ID)
goods_id (商品ID)
quantity (销售数量)
sale_date (销售日期)
base_goods 表结构：
id (商品ID)
name (商品名称)
price (商品价格)
SQL 查询示例
我们需要查询在特定时间段内的销售情况，包括商品名称、销售数量和销售总额。假设我们要查询 2024-01-01 到 2024-12-31 之间的销售情况。

SELECT 
    g.id AS goods_id,
    g.name AS goods_name,
    SUM(s.quantity) AS total_quantity,
    SUM(s.quantity * g.price) AS total_sales
FROM 
    base_sales s
JOIN 
    base_goods g ON s.goods_id = g.id
WHERE 
    s.sale_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY 
    g.id, g.name
ORDER BY 
    total_sales DESC;


在控制器中实现
在 Base.php 控制器中，我们可以实现一个方法来执行这个查询，并返回结果。

public function getSalesReport()
{
    if (Request::isPost()) {
        $data = Request::post(['start_date', 'end_date']);
        try {
            validate(SalesValidate::class)->check($data);
        } catch (ValidateException $exception) {
            $judge = $exception->getMessage();
            return $this->show(
                config('status.error'),
                config('status.error'),
                $judge
            );
        }

        $start_date = $data['start_date'];
        $end_date = $data['end_date'];

        $result = Db::name('base_sales')
            ->alias('s')
            ->join('base_goods g', 's.goods_id = g.id')
            ->whereBetween('s.sale_date', [$start_date, $end_date])
            ->field('g.id as goods_id, g.name as goods_name, SUM(s.quantity) as total_quantity, SUM(s.quantity * g.price) as total_sales')
            ->group('g.id, g.name')
            ->order('total_sales', 'desc')
            ->select();

        return json([
            'code' => 0,
            'msg' => '',
            'data' => $result
        ]);
    }

    $data['url'] = request()->pathinfo();
    View::assign(['data' => $data]);
    return View::fetch();
}


解释
JOIN：
使用 join 方法将 base_sales 表与 base_goods 表连接起来，连接条件是 s.goods_id = g.id。
WHERE BETWEEN：
使用 whereBetween 方法筛选在指定时间段内的销售记录。
字段选择和聚合函数：
使用 field 方法选择需要的字段，并使用聚合函数 SUM 计算销售数量和销售总额。
GROUP BY：
使用 group 方法按商品ID和商品名称分组，以便计算每个商品的销售情况。
ORDER BY：
使用 order 方法按销售总额降序排列结果。
返回结果：
将查询结果封装到一个数组中，并返回 JSON 格式的数据。
完整示例
以下是完整的控制器方法示例，包括分页查询和获取总记录数：

public function getSalesReport()
{
    if (Request::isPost()) {
        $data = Request::post(['start_date', 'end_date', 'limit', 'page']);
        try {
            validate(SalesValidate::class)->check($data);
        } catch (ValidateException $exception) {
            $judge = $exception->getMessage();
            return $this->show(
                config('status.error'),
                config('status.error'),
                $judge
            );
        }

        $start_date = $data['start_date'];
        $end_date = $data['end_date'];
        $page = isset($data['page']) ? $data['page'] : 1;
        $limit = isset($data['limit']) ? $data['limit'] : 10;
        $offset = ($page - 1) * $limit;

        $salesData = Db::name('base_sales')
            ->alias('s')
            ->join('base_goods g', 's.goods_id = g.id')
            ->whereBetween('s.sale_date', [$start_date, $end_date])
            ->field('g.id as goods_id, g.name as goods_name, SUM(s.quantity) as total_quantity, SUM(s.quantity * g.price) as total_sales')
            ->group('g.id, g.name')
            ->order('total_sales', 'desc')
            ->limit($limit)
            ->offset($offset)
            ->select();

        // 获取总记录数
        $total = Db::name('base_sales')
            ->alias('s')
            ->join('base_goods g', 's.goods_id = g.id')
            ->whereBetween('s.sale_date', [$start_date, $end_date])
            ->field('g.id as goods_id, g.name as goods_name')
            ->group('g.id, g.name')
            ->count();

        // 返回结果
        $result = [
            'code' => 0,
            'msg' => '',
            'count' => $total,
            'data' => $salesData
        ];

        return json($result);
    }

    $data['url'] = request()->pathinfo();
    View::assign(['data' => $data]);
    return View::fetch();
}


解释
JOIN：
使用 join 方法将 base_sales 表与 base_goods 表连接起来，连接条件是 s.goods_id = g.id。
WHERE BETWEEN：
使用 whereBetween 方法筛选在指定时间段内的销售记录。
字段选择和聚合函数：
使用 field 方法选择需要的字段，并使用聚合函数 SUM 计算销售数量和销售总额。
GROUP BY：
使用 group 方法按商品ID和商品名称分组，以便计算每个商品的销售情况。
ORDER BY：
使用 order 方法按销售总额降序排列结果。
分页查询：
使用 limit 和 offset 方法进行分页查询。
总记录数：
使用 count 方法获取总记录数，确保分页功能的正确性。
返回结果：
将查询结果和总记录数封装到一个数组中，并返回 JSON 格式的数据。
通过这种方式，你可以在控制器中实现带有多个 JOIN 的分页查询，并获取总记录数，从而统计商品在特定时间段内的销售情况。