27,580
社区成员
发帖
与我相关
我的任务
分享
select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t
select
dateadd(dd,num,@sdate) dt
declare @sdt datetime,@edt datetime
select @sdt='2012-2-20',@edt='2012-2-26'
select a.*,isnull(b.数量,0) as 数量总计
from
(
select convert(varchar(10),dateadd(dd,number,@sdt),120) as 日期,a.生产线
from (select distinct 生产线 from tb) a,master..spt_values b
where type='P'
and dateadd(dd,number,@sdt)<=@edt
) a
left join
(
select convert(varchar(10),日期,120) as 日期,生产线,sum(产品数量) as 数量
from tb
group by convert(varchar(10),日期,120),生产线
) b
on a.日期=b.日期 and a.生产线=b.生产线
获取两个时间之内的所有日期
--sql 2000
declare @sdate datetime
declare @edate datetime
set @sdate = '2008-10-15 00:00:000'
set @edate = '2009-02-10 00:00:000'
select
dateadd(dd,num,@sdate) dt
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,@sdate)<=@edate
--sql 2005
declare @startDate datetime
declare @endDate datetime
SELECT @startDate = '2008-10-15' ,@endDate = '2009-01-23'
;WITH tb AS (
SELECT @startDate AS 'date'
UNION ALL
SELECT DATEADD(DAY,1,date) FROM tb WHERE DATE<@endDate
)
SELECT tb.date from tb
生成日期的方法
方法一、
declare @t table(s datetime)
declare @s datetime,@s1 datetime
set @s = '2010-09-15'
set @s1 = '2010-10-15'
while @s <= @s1
begin
insert into @t select @s
set @s = dateadd(dd,1,@s)
end
select * from @t
方法二、
select
dateadd(dd,number,'2010-09-15') as s
from
master..spt_values
where
type='p'
and
dateadd(dd,number,'2010-09-15')<='2010-10-15'
--sql 2000
declare @sdate datetime
declare @edate datetime
set @sdate = '2012-02-20 00:00:000'
set @edate = '2012-02-26 00:00:000'
select t1.*, 数量总计 = (select sum(产品数量) from 你的表 t2 where t2.生产线 = t1.生产线 and datediff(dd,t2.日期,t1.dt) = 0) from
(
select m.dt , n.生产线 from
(
select
dateadd(dd,num,@sdate) dt
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,@sdate)<=@edate
) m , 你的表 n
) t1
order by t1.dt 日期 , t1.生产线