php - 获取最近两周的 SQL

Question

我想知道是否有办法在 SQL 中获取最近两周及其数量，我解释说:

以下查询以这种方式向我提供过去两周的产品数量:

SELECT pr_products.product,
       WEEKOFYEAR(pf_harvest.date) AS week, 
       SUM(pf_harvest.quantity) AS quantity
       FROM pf_harvest
       INNER JOIN pr_products ON pr_products.id = pf_harvest.id_product
       WHERE pf_harvest.date BETWEEN '2016-08-15' AND '2016-08-28'
       AND pf_harvest.id_tenant = 1
       AND pf_harvest.id_product = 1
       GROUP BY product, WEEKOFYEAR(pf_harvest.date);

我得到的结果如下:

-------------------------------------
product     |    week  |   quantity |
-------------------------------------
ROSA PINK   |    33    |    1076000 |
-------------------------------------
ROSA PINK   |    34    |    1094025 |
-------------------------------------

很好，问题是我希望查询结果给我留下这样的东西:

-------------------------------------
product     |    week1 |   week2    |
-------------------------------------
ROSA PINK   |  1076000 |    1094025 |
-------------------------------------

这是获取第33周和第34周在一个产品中分组的数量，不知道可不可以这样，谢谢!

Answer 1

您应该使用条件聚合。每列仅汇总其一周。因此，一次查询一次即可获得两个聚合结果。

SELECT pr_products.product,
       SUM(case when WEEKOFYEAR(pf_harvest.date)=43 then pf_harvest.quantity end) week1, 
       SUM(case when WEEKOFYEAR(pf_harvest.date)=44 then pf_harvest.quantity end) week2
FROM pf_harvest
   INNER JOIN pr_products ON pr_products.id = pf_harvest.id_product
WHERE pf_harvest.date BETWEEN '2016-08-15' AND '2016-08-28'
 AND pf_harvest.id_tenant = 1
 AND pf_harvest.id_product = 1
GROUP BY product