declare @tb table(ProductDate datetime, ClassOrder nvarchar(2))
insert into @tb(ProductDate,ClassOrder) values('2013-11-01','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-01','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-01','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-02','中班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-02','中班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-02','中班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-03','夜班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-03','夜班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-03','夜班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-05','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-05','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-05','白班')根据上表查询出如下结果
Monthly, ClassOrder
------------------------------
1 1
2 2
3 3
4 NULL
5 41、Monthly表示@tb表中的ProductDate中的天,其中缺少2013-11-04,但4也要填充入Monthly中;
2、ClassOrder根据@tb表中的ClassOrder,根据生产日期用递增数字表示:
2013-11-01对应第一班,填入1。
2013-11-02对应第二班,填入2。
2013-11-03对应第三班,填入3。
2013-11-05对应第四班,填入4。
以此类推。
insert into @tb(ProductDate,ClassOrder) values('2013-11-01','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-01','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-01','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-02','中班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-02','中班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-02','中班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-03','夜班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-03','夜班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-03','夜班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-05','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-05','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-05','白班')根据上表查询出如下结果
Monthly, ClassOrder
------------------------------
1 1
2 2
3 3
4 NULL
5 41、Monthly表示@tb表中的ProductDate中的天,其中缺少2013-11-04,但4也要填充入Monthly中;
2、ClassOrder根据@tb表中的ClassOrder,根据生产日期用递增数字表示:
2013-11-01对应第一班,填入1。
2013-11-02对应第二班,填入2。
2013-11-03对应第三班,填入3。
2013-11-05对应第四班,填入4。
以此类推。
insert into @tb(ProductDate,ClassOrder) values('2013-11-01','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-01','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-01','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-02','中班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-02','中班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-02','中班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-03','夜班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-03','夜班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-03','夜班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-05','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-05','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-05','白班')/*一个比较笨的办法填充缺数*/
declare @Cur Datetime
,@Max Datetime;select @Cur = MIN( ProductDate ), @Max = MAX( ProductDate ) from @tbwhile @Cur <= @Max
begin
if not exists( select top 1 1 from @tb where ProductDate = @Cur )
insert into @tb(ProductDate,ClassOrder) values(@Cur,NULL)
set @Cur = @Cur + 1;
end
select ProductDate as Monthly,
case when not exists( select top 1 1 from @tb as c where c.ProductDate = a.ProductDate and c.ClassOrder is not null ) then null
else( select COUNT(distinct ProductDate) from @tb as b where b.ProductDate <= a.ProductDate and b.ClassOrder is not null ) end as ClassOrder
from @tb as a group by ProductDate
insert into @tb(ProductDate,ClassOrder) values('2013-11-01','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-01','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-01','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-02','中班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-02','中班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-02','中班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-03','夜班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-03','夜班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-03','夜班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-05','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-05','白班')
insert into @tb(ProductDate,ClassOrder) values('2013-11-05','白班')/*一个比较笨的办法填充缺数*/
declare @Cur Datetime
,@Max Datetime;select @Cur = MIN( ProductDate ), @Max = MAX( ProductDate ) from @tbwhile @Cur <= @Max
begin
if not exists( select top 1 1 from @tb where ProductDate = @Cur )
insert into @tb(ProductDate,ClassOrder) values(@Cur,NULL)
set @Cur = @Cur + 1;
end
select DATEPART( DAY, ProductDate ) as Monthly,
case when not exists( select top 1 1 from @tb as c where c.ProductDate = a.ProductDate and c.ClassOrder is not null ) then null
else( select COUNT(distinct ProductDate) from @tb as b where b.ProductDate <= a.ProductDate and b.ClassOrder is not null ) end as ClassOrder
from @tb as a group by ProductDate