获取最近两周的SQL
获取最近两周的SQL
提问于 2016-09-01 21:02:32
我想知道是否有一种方法可以在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周的数量，我不知道是否可以这样做，谢谢！
回答 4
Stack Overflow用户
回答已采纳
发布于 2016-09-01 21:07:40
您应该使用条件聚合。每一列仅聚合其所在的一周。因此，一次查询一次就可以得到两个聚合。
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 productStack Overflow用户
发布于 2016-09-01 21:07:55
为了实现这样的功能，使用了联合选择
SELECT pr_products.product,
SUM(pf_harvest.quantity) AS week1
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-21'
AND pf_harvest.id_tenant = 1
AND pf_harvest.id_product = 1
GROUP BY product, WEEKOFYEAR(pf_harvest.date);
UNION
SELECT
SUM(pf_harvest.quantity) AS week2
FROM pf_harvest
INNER JOIN pr_products ON pr_products.id = pf_harvest.id_product
WHERE pf_harvest.date BETWEEN '2016-08-21' AND '2016-08-28'
AND pf_harvest.id_tenant = 1
AND pf_harvest.id_product = 1
GROUP BY product, WEEKOFYEAR(pf_harvest.date);第一个select语句获取第一周的数据，第二个select语句获取第二周的数据。
有关联合的更多信息，可以在mysql上阅读
Stack Overflow用户
发布于 2016-09-01 21:10:32
我确信这不是最好的方法，但您可以使用偏移合并来引入它。如果内存正常，这应该将字段标记为product|week|week(1)|week(2)|....
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39272284
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号