sql查询获取两个产品每月的第一条和最后一条记录

发布于 2025-01-11 12:44:35 字数 590 浏览 0 评论 0原文

大家好，我正在编写一个查询，可以为我提供每个月的第一个和最后一个记录，但情况是我正在使用下面的代码检索两个产品的数据，

SELECT u1.product, 
       v.month,
       v.mindt,
       u1.opens,
       v.maxdt,
       u2.closingStockvalue 
FROM ( SELECT month, MIN(date) mindt, MAX(date) maxdt 
       FROM closingstock 
       where product='PMS' 
       GROUP BY month) v 
JOIN closingstock u1 ON u1.date=v.mindt 
JOIN closingstock u2 ON u2.date=v.maxdt;

但结果在下面的屏幕截图中我只想要突出显示的结果，请提供任何解决方案，屏幕截图如下在此处输入图片说明

原文

hello guys please i'm writing a query that can give me the first and last records of each month which is done but the case is i'm retrieving data for two products using the code below

SELECT u1.product, 
       v.month,
       v.mindt,
       u1.opens,
       v.maxdt,
       u2.closingStockvalue 
FROM ( SELECT month, MIN(date) mindt, MAX(date) maxdt 
       FROM closingstock 
       where product='PMS' 
       GROUP BY month) v 
JOIN closingstock u1 ON u1.date=v.mindt 
JOIN closingstock u2 ON u2.date=v.maxdt;

but the result is in the screen shot below and i want only the highlighted result please any solution for that the screenshot is below
enter image description here

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

盛夏已如深秋| 2025-01-18 12:44:35

SELECT product, 
       v.month,
       v.mindt,
       u1.opens,
       v.maxdt,
       u2.closingStockvalue 
FROM ( SELECT product, `month`, MIN(`date`) mindt, MAX(`date`) maxdt 
       FROM closingstock 
       WHERE product IN ('Product 1', 'Product 2')  -- the list of needed products
       GROUP BY product, `month`) v 
JOIN closingstock u1 USING (product)
JOIN closingstock u2 USING (product)
WHERE u1.`date`=v.mindt
  AND u2.`date`=v.maxdt;

SELECT product, 
       v.month,
       v.mindt,
       u1.opens,
       v.maxdt,
       u2.closingStockvalue 
FROM ( SELECT product, `month`, MIN(`date`) mindt, MAX(`date`) maxdt 
       FROM closingstock 
       WHERE product IN ('Product 1', 'Product 2')  -- the list of needed products
       GROUP BY product, `month`) v 
JOIN closingstock u1 USING (product)
JOIN closingstock u2 USING (product)
WHERE u1.`date`=v.mindt
  AND u2.`date`=v.maxdt;

回复收藏 0 原文

~没有更多了~