有一张表A
year month day count 2009 12 30 54
2009 12 31 41
2010 1 1 174
2010 1 2 23
...
year属性为年
month属性为月
day属性为日
count属性为人数
我想按随便两个时间段查询人数,比如从“2009年12月30日”到“2010年1月2日”的人数统计
year month day count 2009 12 30 54
2009 12 31 41
2010 1 1 174
2010 1 2 23
...
year属性为年
month属性为月
day属性为日
count属性为人数
我想按随便两个时间段查询人数,比如从“2009年12月30日”到“2010年1月2日”的人数统计
if object_id('[t107]') is not null drop table [t107]
create table [t107]([year] int,[month] int,[day] int,[count] int)
insert [t107]
select 2009,12,30,54 union all
select 2009,12,31,41 union all
select 2010,1,1,174 union all
select 2010,1,2,23select * from [t107]select sum([count]) from [t107]
where ltrim([year])+'年'+ltrim([month])+'月'+ltrim([day])+'日' between '2009年12月30日' and '2010年1月2日'
/*292*/
select sum([count]) from a
where cast(ltrim([year])+'-'+ltrim([month])+'-'+ltrim([day]) as datetime)>='2009-12-30'
and cast(ltrim([year])+'-'+ltrim([month])+'-'+ltrim([day]) as datetime)<'2010-1-3 0:00:00'
where cast([year] as varchar(4))+'-'+cast([month] as varchar(4))+'-'+cast([day] as varchar(4))
between '2009-12-30' and '2010-1-1'
declare @t table([year] int,[month] int,[day] int,[count] int)
insert @t
select 2009,12,30,54 union all
select 2009,12,31,41 union all
select 2010,1,1,174 union all
select 2010,1,2,23select * from @t
where cast([year] as varchar(4))+'-'+cast([month] as varchar(4))+'-'+cast([day] as varchar(4))
between '2009-12-30' and '2010-1-1'
/*
year month day count
----------- ----------- ----------- -----------
2009 12 30 54
2009 12 31 41
2010 1 1 174*/
where cast([year] as varchar(4))+'-'+cast([month] as varchar(4))+'-'+cast([day] as varchar(4))
between '2009-12-30' and '2010-1-1'
/*
count
-----------
269*/