SQL Group通过在哪里声明 [英] SQL Group By Having Where Statements

问题描述

我有一个 MS Access 表格跟踪月末的产品数量，如下所示。
我需要为指定的日期产生指定ProductId的最新数量，例如
12月2月15日的 ProductId 1的数量为100 ，15年3月15日的 ProductId 1的数量为150 。

  ProductId | ReportingDate |产品数量| 
 1 | 31-Jan-12 | 100 | 
 2 | 31-Jan-12 | 200 | 
 1 | 28-Feb-12 | 150 | 
 2 | 28-Feb-12 | 250 | 
 1 | 31-Mar-12 | 180 | 
 2 | 31-Mar-12 | 280 | 
  

下面的SQL语句将所有以前的值替换为最新的值。任何人都可以协助我解决查询问题。

 选择Sheet1.ProductId，Max（Sheet1.ReportingDate）AS MaxOfReportingDate，Sheet1.Quantity 
 FROM Sheet1 
 GROUP BY Sheet1.ProductId，Sheet1.Quantity，Sheet1.ReportingDate，Sheet1.ProductId 
 HAVING（（（Sheet1.ReportingDate）&＃^ 3/15/2012＃）AND（ （Sheet1.ProductId）= 1））
  

解决方案

naveen的想法：

  SELECT TOP 1 Sheet1.ProductId，Sheet1.ReportingDate AS MaxOfReportingDate，Sheet1.Quantity 
 FROM Sheet1 
 WHERE（Sheet1.ProductId = 1）
 AND（Sheet1.ReportingDate<＃2012/03/15＃）
 ORDER BY Sheet1.ReportingDate DESC 
  

尽管注意到MsAccess选择顶部关系，所以如果每个 ReportingDate ， ProductId 组合。 （但同时，这意味着数据不是确定性的）

编辑 - 我的意思是如果你有矛盾在下面的数据中，您将返回2行。

  ProductId | ReportingDate |产品数量| 
 1 | 31-Jan-12 | 100 
 1 | 31-Jan-12 | 200 
  

I have a MS Access table tracking quantities of products at end month as below. I need to generate the latest quantity for a specified ProductId at a specified date e.g. The Quantity for ProductId 1 on 15-Feb-12 is 100, The Quantity for ProductId 1 on 15-Mar-12 is 150.

ProductId | ReportingDate | Quantity|  
1         | 31-Jan-12     | 100     |  
2         | 31-Jan-12     | 200     |  
1         | 28-Feb-12     | 150     |  
2         | 28-Feb-12     | 250     |  
1         | 31-Mar-12     | 180     |  
2         | 31-Mar-12     | 280     |

My SQL statement below bring all previous values instead the latest one only. Could anyone assist me troubleshoot the query.

SELECT Sheet1.ProductId, Max(Sheet1.ReportingDate) AS MaxOfReportingDate, Sheet1.Quantity
FROM Sheet1
GROUP BY Sheet1.ProductId, Sheet1.Quantity, Sheet1.ReportingDate, Sheet1.ProductId
HAVING (((Sheet1.ReportingDate)<#3/15/2012#) AND ((Sheet1.ProductId)=1))

解决方案

Here's @naveen's idea:

SELECT TOP 1 Sheet1.ProductId, Sheet1.ReportingDate AS MaxOfReportingDate, Sheet1.Quantity
FROM Sheet1
WHERE (Sheet1.ProductId = 1)
AND (Sheet1.ReportingDate < #2012/03/15#)
ORDER BY Sheet1.ReportingDate DESC

Although note that MsAccess selects top with ties, so this won't work if you have more than one row per ReportingDate, ProductId combo. (But at the same time, this means that the data isn't deterministic anyway)

Edit - I meant that if you have a contradiction in your data like below, you'll get 2 rows back.

ProductId | ReportingDate | Quantity|  
1         | 31-Jan-12     | 100    
1         | 31-Jan-12     | 200    

这篇关于SQL Group通过在哪里声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！