27,579
社区成员
发帖
与我相关
我的任务
分享
create table tb(年 int,月 int,卡类 int ,折扣 varchar(10))
insert into tb values('2012', 4 ,1 ,'90%')
insert into tb values('2012', 4 ,2 ,'85%')
insert into tb values('2013', 2 ,1 ,'75%')
insert into tb values('2013', 2 ,2 ,'80%')
go
declare @sdate datetime
declare @edate datetime
set @sdate = (select ltrim(min(年)) from tb) + '-01-01'
set @edate = (select max(ltrim(年) + '-' + right('0'+ltrim(月),2)) from tb) + '-01'
select datepart(yy,t1.dt) 年,
datepart(mm,t1.dt) 月,
t1.卡类,
折扣 = (case when datepart(yy,t1.dt) = t2.年 and datepart(mm,t1.dt) = t2.月 and t1.卡类 = t2.卡类 then t2.折扣
else isnull((select top 1 折扣 from tb where 卡类 = t1.卡类 and (ltrim(年) + '-' + right('0'+ltrim(月),2)) < convert(varchar(7),t1.dt,120) order by (ltrim(年) + '-' + right('0'+ltrim(月),2)) desc),'100%') end ) from
(
select m.dt , n.卡类 from
(
select
dateadd(mm,num,@sdate) dt
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(mm,num,@sdate)<=@edate
) m , (select distinct 卡类 from tb) n
) t1 left join tb t2
on datepart(yy,t1.dt) = t2.年 and datepart(mm,t1.dt) = t2.月 and t1.卡类 = t2.卡类
order by datepart(yy,t1.dt),datepart(mm,t1.dt),t1.卡类
drop table tb
/*
年 月 卡类 折扣
----------- ----------- ----------- ----------
2012 1 1 100%
2012 1 2 100%
2012 2 1 100%
2012 2 2 100%
2012 3 1 100%
2012 3 2 100%
2012 4 1 90%
2012 4 2 85%
2012 5 1 90%
2012 5 2 85%
2012 6 1 90%
2012 6 2 85%
2012 7 1 90%
2012 7 2 85%
2012 8 1 90%
2012 8 2 85%
2012 9 1 90%
2012 9 2 85%
2012 10 1 90%
2012 10 2 85%
2012 11 1 90%
2012 11 2 85%
2012 12 1 90%
2012 12 2 85%
2013 1 1 90%
2013 1 2 85%
2013 2 1 75%
2013 2 2 80%
(所影响的行数为 28 行)
*/
create table tb(年 int,月 int,卡类 int ,折扣 varchar(10))
insert into tb values('2012', 4 ,1 ,'90%')
insert into tb values('2012', 4 ,2 ,'85%')
insert into tb values('2013', 2 ,1 ,'75%')
insert into tb values('2013', 2 ,2 ,'80%')
go
declare @sdate datetime
declare @edate datetime
set @sdate = (select ltrim(min(年)) from tb) + '-01-01'
set @edate = (select max(ltrim(年) + '-' + right('0'+ltrim(月),2)) from tb) + '-01'
select datepart(yy,t1.dt) 年,
datepart(mm,t1.dt) 月,
t1.卡类,
折扣 = (case when datepart(yy,t1.dt) = t2.年 and datepart(mm,t1.dt) = t2.月 and t1.卡类 = t2.卡类 then t2.折扣
else isnull((select top 1 折扣 from tb where (ltrim(年) + '-' + right('0'+ltrim(月),2)) < convert(varchar(7),t1.dt,120) order by (ltrim(年) + '-' + right('0'+ltrim(月),2)) desc),'100%') end ) from
(
select m.dt , n.卡类 from
(
select
dateadd(mm,num,@sdate) dt
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(mm,num,@sdate)<=@edate
) m , (select distinct 卡类 from tb) n
) t1 left join tb t2
on datepart(yy,t1.dt) = t2.年 and datepart(mm,t1.dt) = t2.月 and t1.卡类 = t2.卡类
order by datepart(yy,t1.dt),datepart(mm,t1.dt),t1.卡类
drop table tb
/*
年 月 卡类 折扣
----------- ----------- ----------- ----------
2012 1 1 100%
2012 1 2 100%
2012 2 1 100%
2012 2 2 100%
2012 3 1 100%
2012 3 2 100%
2012 4 1 90%
2012 4 2 85%
2012 5 1 85%
2012 5 2 85%
2012 6 1 85%
2012 6 2 85%
2012 7 1 85%
2012 7 2 85%
2012 8 1 85%
2012 8 2 85%
2012 9 1 85%
2012 9 2 85%
2012 10 1 85%
2012 10 2 85%
2012 11 1 85%
2012 11 2 85%
2012 12 1 85%
2012 12 2 85%
2013 1 1 85%
2013 1 2 85%
2013 2 1 75%
2013 2 2 80%
(所影响的行数为 28 行)
*/