SQL从日期范围中选择具有月份名称的表

I have tables named:

我有名为:

Posts_August_2011
Posts_September_2011
Posts_October_2011
Posts_November_2011
Posts_December_2011
Posts_January_2012

I want to create stored procedure that would UNION tables,but that is not the problem since I can use dynamic SQL to glue it up, my problem is getting Months and Year Range from RANGE to some kind of list? and then checking if table exist, Union them and return.

我想创建UNION表的存储过程,但这不是问题,因为我可以使用动态SQL来粘合它,我的问题是将RANGE的Months和Year Range变成某种列表?然后检查表是否存在,联合它们并返回。

I would declare 2 variables @FromDate and @Todate as datetime

我会将2个变量@FromDate和@Todate声明为datetime

What would be the best way to extract month name and year for each of the months in given range.

在给定范围内为每个月提取月份名称和年份的最佳方法是什么。

Say I give it a range @FromDate = '2011-10-18 14:16:27.000' and @ToDate = '2012-01-09 14:16:27.000' can someone advise how to extract Month and year.

假设我给它一个范围@FromDate ='2011-10-18 14:16:27.000'和@ToDate ='2012-01-09 14:16:27.000'有人可以建议如何提取月份和年份。

DECLARE @SqlToExecute as nvarchar(MAX);

using dinamyc SQL is acceptable,It could check for existence of table in while loop and glue to string for each table that exists in range as:

使用dinamyc SQL是可以接受的,它可以在while循环中检查表的存在,并为范围内存在的每个表粘合到字符串:

@SqlToExecute += 'SELECT * FROM Posts_' + extractedMonthName + '_' + extractedYear + ' UNION ALL ';

3 个解决方案

#1

This problem is caused by bad database design. You needed to store rows from all this tables in one table Posts with date field to specify Month and Year.

此问题是由错误的数据库设计引起的。您需要在一个表中存储来自所有这些表的行,并在日期字段中指定月份和年份。

To get names of tables Posts_% and date extracted from their names you can use following code:

要获取表名称Posts_%和从其名称中提取的日期,您可以使用以下代码:

create table Posts_August_2011(i int)
GO
create table Posts_September_2011(i int)
GO

DECLARE @SqlToExecute varchar(max)
DECLARE @FromDate date = '20110801', @ToDate date = '20110809'

SELECT  name PostName, CAST(REPLACE(REPLACE(name,'Posts_','01/'),'_','/') as date) PostDate
INTO #Posts
FROM sys.tables
WHERE name like 'Posts_%'

SET @SqlToExecute=''

SELECT @SqlToExecute += 'SELECT * FROM ' + PostName + ' UNION ALL '
FROM #Posts
WHERE PostDate>=@FromDate
      AND PostDate<@ToDate

SELECT SUBSTRING(@SqlToExecute,1, LEN(@SqlToExecute)-10)

DROP TABLE #Posts
DROP TABLE Posts_August_2011
DROP TABLE Posts_September_2011

sys. tables is system catalog view which returns a row for each table object in the current database.

SYS。 tables是系统目录视图,它为当前数据库中的每个表对象返回一行。

#2

You can use DATENAME(month,date) and YEAR(date) to build your sets of months and years.

您可以使用DATENAME(月,日)和YEAR(日期)来构建您的月份和年份集。

Easiest would be to create a tiny lookuptable that has year/month combinations and filter your date range, then just loop the results and build your query and execute.

最简单的方法是创建一个具有年/月组合并过滤日期范围的微小查找表,然后循环结果并构建查询并执行。

For later you can look into partitioning that table into monthly partitions, if the size of the tables are what makes you split them like that.

稍后您可以查看将该表分区为每月分区,如果表的大小是使您将它们拆分的原因。

#3

Is it possible to put these in a single [Post] table with post date? And partition them if you need to?

是否可以将这些放在带有发布日期的单个[Post]表中?如果需要,可以对它们进行分区?

#1