SQl查询从动态表中获取数据。 [英] SQl Query fetch data from Dynamic Tables.

问题描述

大家好，





我有一个系统，我们在一个月内创建2个表。即tbl_Backup01Jan2014_15Jan2014和tbl_Backup16Jan2014_31Jan2014 ...对于2月表是tbl_Backup01Feb2014_15Feb2014和tbl_Backup16Feb2014_28Feb2014等。所以1年我们创建24个表。



我的查询是什么...我想写一个Store Procedure，它有2个参数FromDate和ToDate。如果我将通过FromDate（1Jan2014）和ToDate（15Feb2014），那么程序将仅从3个表中获取记录，该表必须是tbl_Backup01Jan2014_15Jan2014，tbl_Backup16Jan2014_31Jan2014和tbl_Backup01Feb2014_15Feb2014。





注意：每个月会有24张桌子，2张桌子..



谢谢，

Hi All,


I have a system where We are creating 2 table in a months. i.e tbl_Backup01Jan2014_15Jan2014 and tbl_Backup16Jan2014_31Jan2014... for Feb table is tbl_Backup01Feb2014_15Feb2014 and tbl_Backup16Feb2014_28Feb2014 etc.. so for 1 year we creating 24 tables.

What my query is... I want to write a Store Procedure, which will have 2 Parameters FromDate and ToDate. if I will pass FromDate(1Jan2014) and ToDate(15Feb2014), so procedure will fetch record only from 3 Table that must be tbl_Backup01Jan2014_15Jan2014, tbl_Backup16Jan2014_31Jan2014 and tbl_Backup01Feb2014_15Feb2014.


Note: there would be 24 tables, 2 tables for each month..

Thanks,

推荐答案

要合并来自多个表的数据，您可以使用 UNION [ ^ ]子句。无论日期范围如何，为了使查询具有通用性，您可以使用内联视图。类似于：

To combine the data from several tables you can use UNION[^] clause. And to make the query generic regardless of the date range, you can use an inline view. Something like:

SELECT *
FROM ( SELECT col1, col2... FROM tbl_Backup01Jan2014_15Jan2014 
       UNION ALL
       SELECT col1, col2... FROM tbl_Backup16Jan2014_31Jan2014
       UNION ALL
       SELECT col1, col2... FROM tbl_Backup01Feb2014_15Feb2014 
       UNION ALL
       ... ) a
WHERE a.StartDateCol <= @FromDate 
AND   a.EndDateCol   >= @EndDate;

但是，如果可能，我建议使用一个表的数据。除非数据量巨大，否则从查询和性能的角度来看，这将更加容易。

However, if possible I'd recommend to use a single table for the data. This would be much easier from both query and performance point of view unless the amount of data is huge.

这篇关于SQl查询从动态表中获取数据。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！