如何获取基于月和年(包括月和年)的表中的数据? [英] how to get the data in a table based on month and year(including both month and year) ?

问题描述

大家好，

我有一个必须按月和年检索数据库的要求.我正在使用以下查询

Hi All,

I have a requirement that I have to retrieve data base on month and year.I am using the below query

SELECT  SUM(areasft),DATEPART(year, sdate),DATEPART(month, sdate)  FROM storedata
GROUP BY DATEPART(year, sdate) ,DATEPART(month, sdate)
ORDER BY DATEPART(year, sDate) ,DATEPART(month, sDate)

上面的查询是检索数据，如下所示.
年度月份
51013 2000 1
30970 2007 1
NULL 2007 3
900 2007 6
807 2007 8
NULL 2007年9月
1756 2007 10
7535 2007 11
NULL 2008 1
1193 2008 2
4230 2008 3
350 2008 4
2200 2008 5
4660 2008 6
6685 2008 8

但是我需要显示包括年份在内的所有月份以及与SUM无关的所有年份.我的查询一年仅显示几个月.

请解决此问题.

问候，

N.SRIRAM

The above query is retrieve data like below.
SUM YEAR MONTH
51013 2000 1
30970 2007 1
NULL 2007 3
900 2007 6
807 2007 8
NULL 2007 9
1756 2007 10
7535 2007 11
NULL 2008 1
1193 2008 2
4230 2008 3
350 2008 4
2200 2008 5
4660 2008 6
6685 2008 8

But I need to display including all the months of the year along with year irrespective of SUM . My query only displays only some months in a year.

Please solve this problem.

Regards,

N.SRIRAM

推荐答案

也许创建一个辅助表来填补空白:
> http://ask.metafilter.com/43840/Filling-Sparse-Data-在SQL查询中 [ ^ ]

欢呼

Maybe create an auxillary table to fill the gaps :
http://ask.metafilter.com/43840/Filling-Sparse-Data-In-An-SQL-Query[^]

Cheers

我不是DBA，也不是真正的DBA，但这就是我采用的方式.对于您需要研究的部分，google绝对是您的朋友:

0)创建一个包含所有月份的临时表-称为TEMP_YEARS.

空0 1
NULL 0 2
NULL 0 3
..
NULL 0 12

1)创建一个存储过程，以给定的年份(您指定为参数)更新该临时表-将其命名为sp_YearUpdate

2)将所有数据从您的实际表读入第二个临时表(称为TEMP_DATA)

3)确定TEMP_DATA表中表示的最早年份.

4)最早的一年打电话给sp_YearUpdate

5)将TEMP_YEARS与您的真实表合并(使用union?)

6)增加年份

7)返回第4步(必要时重复)

I''m not a DBA, nor really that skilled in it, but this is the way I''d approach it. For parts you need to research, google is definitely your friend:

0) Create a temporary table with all of the months in it - call it TEMP_YEARS.

Null 0 1
NULL 0 2
NULL 0 3
..
NULL 0 12

1) Create a stored procedure that updates that temp table with a given year (that you specify as a parameter) - call it sp_YearUpdate

2) Read all the data from your real table into a 2nd temp table (call it TEMP_DATA)

3) Determine the earliest year represented in the TEMP_DATA table.

4) Call your sp_YearUpdate with the earliest year

5) Merge TEMP_YEARS with your real table (using a union?)

6) Increment the year

7) Go back to step 4 (repeat as necessary)

这篇关于如何获取基于月和年(包括月和年)的表中的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！