如何按日期进行分组查询 [英] how to make group by query with dates

问题描述

推荐先生，

我使用ms-access作为后端，使用vb.net作为开发Windows应用程序的前端...
我在其中使用查询生成月度报告..
像波纹管

Respected sir/s,

i m using ms-access as backend and vb.net as front end for developing windows application...
In which i m using query for generate monthly report..
like bellow

Item_Name   TotQntyPurchsed    TotQntySale    TotQntyInStock
-------------------------------------------------------------
abc            15                 10              5
xyz            20                 18              2

--------------------------------------------------------------

在这里，所有列都按分组以获取各自的项目总计...

但是我每个月都想要这个..
那我该如何使用group by子句呢，因为销售和购买将每天进行
或
还有其他想法可以实现这一目标...?

请帮我..
谢谢

here all the columns are grouped by for getting respective item total...

but i want this for every month..
then how can i do this with group by clause because sale and perchase will be on daily basis
or
any other idea to achieve this...?

plz help me..
thank you

推荐答案

使用此查询:

use this query:

SELECT 
Item_Name,
YEAR(DatePurchased)*100+MONTH(DatePurchased) AS YearMonth,
Sum(QtyInStock) as TotQtyInStock
FROM Stock
GROUP BY Item_Name,
YEAR(DatePurchased)*100+MONTH(DatePurchased)

然后，您将拥有以下内容:

Then you''d have this:

Item_Name   YearMonth  TotQntyInStock
-------------------------------------------------------------
abc         201201      5
abc         201202      2
abc         201203      1
xyz         201201      1
xyz         201205      5
etc...
--------------------------------------------------------------

这篇关于如何按日期进行分组查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！