DECLARE @frdate varchar(50) ;
DECLARE @todate varchar(50) ;
如果两变量 给值如下
set @frdate='2013-03-01';
set @todate='2013-03-20';表testtb中有字段为
nmshow frdt todt
1 2013-02-28 2013-03-02
3 2013-03-01 2013-03-01
8 2013-03-06 2013-03-07要得到从@frdate到@todate的每一天在frdt todt之间的nmshow之和
2013-03-01 4
2013-03-02 1
2013-03-03 0
2013-03-04 0
2013-03-05 0
2013-03-06 8
2013-03-07 8
..................
2013-03-20 0
以次类推,得怎么写select 语句 ?
DECLARE @todate varchar(50) ;
如果两变量 给值如下
set @frdate='2013-03-01';
set @todate='2013-03-20';表testtb中有字段为
nmshow frdt todt
1 2013-02-28 2013-03-02
3 2013-03-01 2013-03-01
8 2013-03-06 2013-03-07要得到从@frdate到@todate的每一天在frdt todt之间的nmshow之和
2013-03-01 4
2013-03-02 1
2013-03-03 0
2013-03-04 0
2013-03-05 0
2013-03-06 8
2013-03-07 8
..................
2013-03-20 0
以次类推,得怎么写select 语句 ?
解决方案 »
- 在sql2005下的查询返回问题
- 急请指点:表中内容改变,怎么用TRIGGER把变动的内容 插入另一张表
- 求一过滤时间后的数据?
- 按ID选出表里倒数第N条
- 安装SQL2000的问题?急救。。。。
- "alter table mytable add bmlx char(2) not null "这句有错吗?
- 用case 表达式的Transact-SQL 语句出问题,高手请指点一下,谢谢!
- ODBC连接超时,要延长连接时间,如何设置?
- 求:概念模型、物理模型是用什么软件设计的,后缀是cdb,cdm,pdm
- 请大家讨论:分析触发器对DBMS资源的消耗情况?
- 求一条查询SQL语句
- sql sever 存储过程 出现关键字 'into' 附近有语法错误。
declare @begin date = '2013-1-1'
declare @end date = '2013-1-9'
declare @curDate date
select @curDate = @begin
print convert(varchar(10),@curDate,21)
while(@curDate < @end )
begin
select @curDate = DATEADD(DAY,1,@curDate)
print convert(varchar(10),@curDate,21)
-- 业务数据查询语句
end ;
select * into #t from(
select 1 nmshow, convert(datetime,'2013-02-28') frdt, convert(datetime,'2013-03-02') todt
union all select 3, '2013-03-01', '2013-03-01'
union all select 8, '2013-03-06', '2013-03-07'
)aDECLARE @frdate varchar(50) ;
DECLARE @todate varchar(50) ;
set @frdate='2013-03-01';
set @todate='2013-03-20';;with cte as(
select dateadd(day,number,@frdate) time
from master..spt_values where type='P' and number<31
)
,cte1 as
(select c.*,isnull(a.num,0)num from cte c outer apply (select sum(nmshow)num from #t where frdt<=c.time and todt>=c.time)a
where time<=@todate)
select * from cte1/*
time num
2013-03-01 00:00:00.000 4
2013-03-02 00:00:00.000 1
2013-03-03 00:00:00.000 0
2013-03-04 00:00:00.000 0
2013-03-05 00:00:00.000 0
2013-03-06 00:00:00.000 8
2013-03-07 00:00:00.000 8
2013-03-08 00:00:00.000 0
2013-03-09 00:00:00.000 0
2013-03-10 00:00:00.000 0
2013-03-11 00:00:00.000 0
2013-03-12 00:00:00.000 0
2013-03-13 00:00:00.000 0
2013-03-14 00:00:00.000 0
2013-03-15 00:00:00.000 0
2013-03-16 00:00:00.000 0
2013-03-17 00:00:00.000 0
2013-03-18 00:00:00.000 0
2013-03-19 00:00:00.000 0
2013-03-20 00:00:00.000 0
*/
DECLARE @todate varchar(50) ;set @frdate='2013-03-01';
set @todate='2013-03-20';SELECT DATEADD(DD,NUMBER,@frdate) DT
from master..spt_values where type='p'
and number<=DATEDIFF(dd,@frdate,@todate)SELECT DT,SUM(CASE WHEN DT BETWEEN FRDT END TODT THEN NMSHOW ELSE 0 END) NMSHOW
FROM
(SELECT DATEADD(DD,NUMBER,@frdate) DT
from master..spt_values where type='p'
and number<=DATEDIFF(dd,@frdate,@todate)) T,testtb tb
where t.dt>=tb.frdt