Archives for 5月 2012

利用SQL语句，查询某一年的所有天，可利用此结果查找报表数据中缺少的那一天。

[sql collapse=”false”]
–推荐方法
with tab as
(
select dateadd(day,ROW_NUMBER()over(order by GETDATE())-1,cast(‘2012-1-1’ as datetime)) as col
from sys.all_columns
)
select * from tab
where col<‘2013-01-01’

–下面方法不能查询出缺少的最后一天，但是查找中间的某一天还是很方便的
create table t
(
col date
)

insert into t select ‘2012-01-01’
insert into t select ‘2012-01-03’
insert into t select ‘2012-01-04’
insert into t select ‘2012-01-05’

select * from t a
left join t b on dateadd(day,1,a.col)=b.col

–较麻烦，但是能达到同样的效果，在没有权限操作系统表，也没有表的数据量大于要查询的天数时，可利用下面的方法

with tab as
(
select a1.id+a2.id+a3.id+a4.id+a5.id+a6.id+a7.id+a8.id+a9.id as id from
(select 0 as id union all select 1 )a1
cross join
(select 0 as id union all select 2 )a2
cross join
(select 0 as id union all select 4 )a3
cross join
(select 0 as id union all select 8 )a4
cross join
(select 0 as id union all select 16 )a5
cross join
(select 0 as id union all select 32 )a6
cross join
(select 0 as id union all select 64 )a7
cross join
(select 0 as id union all select 128 )a8
cross join
(select 0 as id union all select 256 )a9
)
select dateadd(day,id,cast(‘2012-1-1’ as datetime))
as col
from tab
where dateadd(day,id,cast(‘2012-1-1’ as datetime))<‘2013-01-01’
order by 1

[/sql]