sqlserver区间,sql 时间区间

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

SQLSERVER 列转行

第一：你的时间维度表基本没有意义，微软SSIS中心认为时间维度至少由日期构成主键。可以认为是最小基本业务颗粒。

来个Sample，更复杂的在我空间里。但是道理是一样的。都是借助动态SQL和一些函数。

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

作者:Edwin

数据库:SQL SERVER 2005+

作用:指定时间区间的自然周有几天，如果垮年度，由外围验证

Version 1.0

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

/*参数设定区域,参数为开始时间和结束时间*/

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

declare @FDate DateTime set @FDate = '2014-09-01 00:00:00.000'

declare @Edate DateTime set @Edate = '2015-01-01 00:00:00.000'

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

/*SQL主体*/

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

declare @WeekHeader nvarchar(max)

select @WeekHeader = coalesce(@WeekHeader+',['+cast(WeekOfYear as varchar)+']','['+cast(WeekOfYear as varchar)+ ']')

from

(

select WeekOfYear from Comn.Calendar where DatePerDay=@FDate and DatePerDay@Edate group by WeekOfYear

) M

declare @PivotSQL nvarchar(max) set @PivotSQL=N'

select

Year as 年份,'+@WeekHeader+'

from

(

select [Year],WeekOfYear,DatePerDay from [DT_WareHouse].[Comn].[Calendar] where DatePerDay=@FDate and DatePerDay@Edate

) M

pivot

(

count(DatePerDay) for [WeekOfYear] in('+@WeekHeader+')

) PVT'

exec sp_executesql @PivotSQL,N'@FDate datetime,@Edate datetime',@FDate,@Edate

执行结果：

在看一下时间维度表：

至于PVT标题别名问题，这个可以在时间维度表中创建字符串类型的第几周等样式的列来完成。

这种方式比较简单。

或是

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

作者:Edwin

数据库:SQL SERVER 2005+

作用:指定时间区间的自然周销售,统计周期为某一年，如果垮年度，由外围验证

Version 1.0

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

/*参数设定区域,参数为开始时间和结束时间*/

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

declare @FDate DateTime set @FDate = '2014-09-01 00:00:00.000'

declare @Edate DateTime set @Edate = '2015-01-01 00:00:00.000'

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

/*SQL主体*/

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

declare @WeekHeader nvarchar(max)

select @WeekHeader = coalesce(@WeekHeader+',['+cast(WeekOfYear as varchar)+']','['+cast(WeekOfYear as varchar)+ ']')

from

(

select ('第'+cast(WeekOfYear as varchar)+'周') as WeekOfYear from Comn.Calendar where DatePerDay=@FDate and DatePerDay@Edate group by WeekOfYear

) M

declare @PivotSQL nvarchar(max) set @PivotSQL=N'

select

Year as 年份,'+@WeekHeader+'

from

(

select [Year],(''第''+cast(WeekOfYear as varchar)+''周'') as WeekOfYear,DatePerDay from [DT_WareHouse].[Comn].[Calendar] where DatePerDay=@FDate and DatePerDay@Edate

) M

pivot

(

count(DatePerDay) for [WeekOfYear] in('+@WeekHeader+')

) PVT'

exec sp_executesql @PivotSQL,N'@FDate datetime,@Edate datetime',@FDate,@Edate

结果：

相关问答

Q1: 按照时间区间查询数据库的问题

select * from 表 where 时间='统计其实时间' and 时间=‘统计结束时间’

Q2: sqlserver2008怎么实现自动分区表

/*第一步：创建分区函数*/

Create partition function Part_func_Bag(varchar(20)) as range right

/*正式区间

for values(N'01100923909760', N'01100936207030', N'6311001806524',N'92451400060101');*/for values(N'91701311710807', N'07201280707101', N'8011011089884',N'80241001430714');goselect * from Bag where BagCode in(N'91701311710807', N'07201280707101', N'8011011089884',N'80241001430714')

/*第二步：创建文件组和文件*/

alter database ZXAutoCode add filegroup [Bag_1]; alter database ZXAutoCode add filegroup [Bag_2];alter database ZXAutoCode add filegroup [Bag_3];alter database ZXAutoCode add filegroup [Bag_4];goalter database ZXAutoCode add file (name = Bag1_data,filename = 'E:\MSSQL\TESTDATA\Bag1_data.ndf',size = 3MB) to filegroup [Bag_1];alter database ZXAutoCode add file (name = Bag2_data,filename = 'E:\MSSQL\TESTDATA\Bag2_data.ndf',size = 3MB) to filegroup [Bag_2];alter database ZXAutoCode add file (name = Bag3_data,filename = 'E:\MSSQL\TESTDATA\Bag3_data.ndf',size = 3MB) to filegroup [Bag_3]; alter database ZXAutoCode add file (name = Bag4_data,filename = 'E:\MSSQL\TESTDATA\Bag4_data.ndf',size = 3MB) to filegroup [Bag_4]; go

/*第三步：创建分区方案并关联到分区函数*/

Create partition scheme Part_func_Bag_scheme as partition Part_func_Bag to ([Bag_1],[Bag_2],[Bag_3],[Bag_4],[Primary]); go

/*第四步重建索引（删除聚集索引以及需要分区字段的索引后重建该类索引，表被按分区值将分配到各文件组。数据在这一步开始转移。）*/

EXEC sp_helpindex N'Bag' --查看orders中使用的索引 drop index idx_cl_od on Bag;gocreate clustered index idx_cl_od on Bag(bagcode) on Part_func_Bag_scheme(bagcode); go

Q3: 数据库中，between…and相当于数学中的开区间还是闭区间？急！！！

between

and

和

=2and=4

是等价的，-------------每种数据库处理方式不一样，这种是sql的处理方式;mysql+sqlserver

oracle中between and也包含边界值，也就是说包含两个端的数，前后都是

闭区间

。

Q4: java 按照时间区间查询sqlserver2000的问题

select count(*)

from Complaints d