php - 产品列表查询中月销售额

Question

谁能帮我解决复杂的sql请求？

我有一个带有产品列表的 html 页面，我想为每个项目添加过去 4 个月的装载和销售信息。

我想要的结果是:Item1 - Month0 (12 in, 0 out), Month-1 (33 in, 36 out)……Item2 - Month0 (10 in, 30 out), Month-1 (0 in, 66 out)……

我的 SQL 查询:

方法“getProducts”中的产品(简化):

Select item_id, item_name
From Products

加载中

SELECT item_id, CONCAT(YEAR(`load_date`), MONTH(`load_date`)) AS YearMonth,SUM(load_qty) AS total,
            FROM loading
            GROUP BY YearMonth
            WHERE item_id = ?
            ORDER BY YearMonth DESC ', array($productId));

销售

SELECT item_id, CONCAT(YEAR(`load_date`), MONTH(`load_date`)) AS YearMonth,SUM(load_qty) AS total,
            FROM sales
            GROUP BY YearMonth
            WHERE item_id = ?
            ORDER BY YearMonth DESC ', array($productId));

然后我得到了 PHP 文件来调用 SQL 方法:

$products = $productManager->getProducts();
$this->data['portalProducts'] = $portalProducts;

以及带有产品列表的最终 HTML 文件:

<?php foreach ($products as $product): ?>
<table>
    <tr>
        <td><?php echo date("Y - m", strtotime("now"))?></td> //current month
        <td>//here goes current month load</td>
        <td>//here goes current month sale</td>
    </tr>
    <tr>
        <td><?php echo date("Y - m", strtotime("-1 month"))?></td> //last month
        <td>//here goes last month load</td>
        <td>//here goes last month sale</td>
    </tr>
    ..............
</table>

是否可以将上述查询的所有结果合并到一个请求中，然后像我在开头提到的那样显示它...提前致谢。

Answer 1

您可以按如下方式组合您的 SQL 查询:

SELECT p.item_id, p.item_name, cal.year_month, COALESCE( q_in.total, 0 ) AS qty_in, COALESCE( q_out.total, 0 ) AS qty_out
FROM Products AS p
    INNER JOIN
                ( SELECT CONCAT(YEAR(NOW()), MONTH(NOW())) AS `year_month`
                UNION
                SELECT CONCAT(YEAR(NOW() - INTERVAL 1 MONTH), MONTH(NOW() - INTERVAL 1 MONTH)) AS `year_month` ) AS `cal`
            ON 1 = 1
        LEFT JOIN
                    ( SELECT `item_id`, CONCAT(YEAR(`load_date`), MONTH(`load_date`)) AS `year_month`, SUM(`load_qty`) AS `total`,
                    FROM loading
                    WHERE (`load_date` BETWEEN LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY AND NOW())
                    GROUP BY `item_id`, YEAR(`load_date`), MONTH(`load_date`)) AS q_in
                ON p.item_id = q_in.item_id AND cal.year_month = q_in.year_month
        LEFT JOIN
                    ( SELECT item_id, CONCAT(YEAR(`load_date`), MONTH(`load_date`)) AS `year_month`, SUM(`load_qty`) AS `total`,
                    FROM sales
                    WHERE (`load_date` BETWEEN LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY AND NOW())
                    GROUP BY `item_id`, YEAR(`load_date`), MONTH(`load_date`)) AS q_out
                ON p.item_id = q_out.item_id AND cal.year_month = q_out.year_month
ORDER BY p.item_id, p.item_name, q_in.year_month

输出示例:

-------------------------------------------------------
| item_id | item_name | year_month | qty_in | qty_out |
-------------------------------------------------------
| 1       | blah      | 201604     | 0      | 16      |
-------------------------------------------------------
| 1       | blah      | 201605     | 12     | 16      |
-------------------------------------------------------

说明:

我使用“日历”子查询来生成月份列表，这样即使给定月份的总数量为 0 进 0 出，您的输出也始终包含当前和前几个月的行:

( SELECT CONCAT(YEAR(NOW()), MONTH(NOW())) AS YearMonth
                            UNION
                            SELECT CONCAT(YEAR(NOW() - INTERVAL 1 MONTH), MONTH(NOW() - INTERVAL 1 MONTH)) AS YearMonth ) AS Cal

然后我使用左连接来连接“loading”和“sales”的查询。

我添加了一个

WHERE (load_date BETWEEN LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY AND NOW())

到每个子查询以将计算限制为当前和上个月。这是为了提高性能。

注意:我没有在MySQL中测试过查询