[分组生成到本月为止的数据 SQL]

Question

--SQL 语句如下
CREATE TABLE #TMP
(
  ID VARCHAR(10)
  PERIOD VARCHAR(7),
  NUM INT   
)INSERT INTO #TMP SELECT 'GB001','2011-09',4
INSERT INTO #TMP SELECT 'GB002','2011-10',3SELECT * FROM #TMP
DROP TABLE #TMP
实现效果如下图, 其中NUM=(当前月-ID对应的PERIOD)* NUM

Answer 1

CREATE TABLE #TMP
(
  ID VARCHAR(10),
  PERIOD VARCHAR(7),
  NUM INT   
)INSERT INTO #TMP SELECT 'GB001','2011-09',4
INSERT INTO #TMP SELECT 'GB002','2011-10',3SELECT 
ID ,PERIOD,
ISNULL((SELECT NUM FROM #TMP WHERE ID=a.ID AND DATEDIFF(m,PERIOD+'-01',a.PERIOD+'-01')=1),0)*a.NUM AS NUM
FROM #TMP AS aDROP TABLE #TMP

Answer 2

CREATE TABLE #TMP
(
  ID VARCHAR(10),
  PERIOD VARCHAR(7),
  NUM INT   
)INSERT INTO #TMP SELECT 'GB001','2011-09',4
INSERT INTO #TMP SELECT 'GB002','2011-10',3
go
;with cte as(
select *,0 as flg from #TMP a where not exists(select 1 from #TMP where ID=a.id and period<a.period)
union all
select id,CONVERT(varchar(7),dateadd(mm,1,period+'-01'),120),num,flg+1 from cte where CONVERT(varchar(7),dateadd(mm,1,period+'-01'),120)<=CONVERT(varchar(7),GETDATE(),120)
)select ID,PERIOD,NUM*flg num from cte
/*
ID         PERIOD  num
---------- ------- -----------
GB001      2011-09 0
GB002      2011-10 0
GB002      2011-11 3
GB001      2011-10 4
GB001      2011-11 8*/
go
SELECT * FROM #TMP
DROP TABLE #TMP

Answer 3

SELECT DATEADD(m,number, CAST(period+'-01' as datetime)) 
,DATEDIFF(m, CAST(period+'-01' as datetime),DATEADD(m,number, CAST(period+'-01' as datetime))) *NUM 
FROM #TMP ,master..spt_values where type='P' AND DATEPART(M , DATEADD(m,number, CAST(period+'-01' as datetime)) ) <=datepart(M,GETDATE())
 AND DATEPART(YYYY  , DATEADD(m,number, CAST(period+'-01' as datetime)) ) <=datepart(YYYY ,GETDATE())

Answer 4

CREATE TABLE #TMP
(
  ID VARCHAR(10),
  PERIOD VARCHAR(7),
  NUM INT   
)INSERT INTO #TMP SELECT 'GB001','2011-09',4
INSERT INTO #TMP SELECT 'GB002','2011-10',3--SELECT * FROM #TMPdeclare @t datetime
set @t='2011-09-01'
;with f as
(
select
 PERIOD=convert(varchar(7),dif,120) 
from 
(
    select dif=dateadd(month,number,@t) from master..spt_values where type='p' 
)t
where dif<getdate()
),
f1 as
(
select * from f cross join (select distinct ID from #TMP)b 
)select
  a.*,
  DATEDIFF(mm,b.PERIOD+'-01',a.PERIOD+'-01')*b.num
 from
  f1 a left join #TMP b
on
  a.ID=b.IDDROP TABLE #TMP/*PERIOD  ID         
------- ---------- -----------
2011-09 GB001      0
2011-10 GB001      4
2011-11 GB001      8
2011-09 GB002      -3
2011-10 GB002      0
2011-11 GB002      3(6 行受影响)
*/

Answer 5

CREATE TABLE #TMP
(
  ID VARCHAR(10),
  PERIOD VARCHAR(7),
  NUM INT   
)INSERT INTO #TMP SELECT 'GB001','2011-09',4
INSERT INTO #TMP SELECT 'GB002','2011-10',3SELECT 
ID,
PERIOD=CONVERT(VARCHAR(7),DATEADD(m,b.langid,a.PERIOD+'-01'),120),
NUM=b.langid*NUM
FROM #TMP AS a,master.sys.syslanguages AS b 
WHERE DATEDIFF(m,a.PERIOD+'-01',DATEADD(m,-b.langid,GETDATE()))>=0
ORDER BY 1,2
/*
ID PERIOD NUM
GB001 2011-09 0
GB001 2011-10 4
GB001 2011-11 8
GB002 2011-10 0
GB002 2011-11 3
*/

Answer 6

CREATE TABLE #TMP
(
  ID VARCHAR(10),
  PERIOD VARCHAR(7),
  NUM INT   
)INSERT INTO #TMP SELECT 'GB001','2011-09',4
INSERT INTO #TMP SELECT 'GB002','2011-10',3--SELECT * FROM #TMPdeclare @t datetime
set @t='2011-09-01'
;with f as
(
select
 PERIOD=convert(varchar(7),dif,120) 
from 
(
    select dif=dateadd(month,number,@t) from master..spt_values where type='p' 
)t
where dif<getdate()
),
f1 as
(
select * from f cross join (select distinct ID from #TMP)b 
)select
  a.*,
  DATEDIFF(mm,b.PERIOD+'-01',a.PERIOD+'-01')*b.num
 from
  f1 a left join #TMP b
on
  a.ID=b.ID
where
  DATEDIFF(mm,b.PERIOD+'-01',a.PERIOD+'-01')>=0
DROP TABLE #TMP/*PERIOD  ID         
------- ---------- -----------
2011-09 GB001      0
2011-10 GB001      4
2011-11 GB001      8
2011-10 GB002      0
2011-11 GB002      3(5 行受影响)
*/

Answer 7

昨天发完帖，就开会去了。
抱歉~我的需求以上都能解决，下面这条我很中意，很简短。唯一不足就是 ,master.sys.syslanguages langid 只有33条，如果生成2008-01 到现在的额月份就不行了。 
SELECT 
ID,
PERIOD=CONVERT(VARCHAR(7),DATEADD(m,b.langid,a.PERIOD+'-01'),120),
NUM=b.langid*NUM
FROM #TMP AS a,master.sys.syslanguages AS b 
WHERE DATEDIFF(m,a.PERIOD+'-01',DATEADD(m,-b.langid,GETDATE()))>=0

Answer 8

用master..spt_values的type='p'的，有2048条

Answer 9

declare @t table(m varchar(10),v int);
insert into @t select '2011-02-05',1
union all select '2011-02-27',2
union all select '2011-03-12',3 
union all select '2011-03-25',1
union all select '2011-05-05',6;
--select * from @t;select case when DAY(m)>26 then MONTH(m)+1 else MONTH(m) end m ,sum(v) c from @t group by case when DAY(m)>26 then MONTH(m)+1 else MONTH(m) end
/*
m           c
----------- -----------
2           1
3           6
5           6
*/