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

[雷肿么了]

--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',3

SELECT * FROM #TMP
DROP TABLE #TMP

实现效果如下图, 其中NUM=(当前月-ID对应的PERIOD)* NUM

[中国风]

11月的數據在那?

[中国风]

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 
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 a

DROP TABLE #TMP

[-晴天]

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

[jwdream2008]

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())

[--小F--]

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 #TMP

declare @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





DROP 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 行受影响)
*/

[中国风]

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 
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
*/

[--小F--]

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 #TMP

declare @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 行受影响)
*/

[QQHRSWB]

我追加个问题，如上题，如何能在取出每月26日为分界线的数据呢？

[雷肿么了]

昨天发完帖，就开会去了。
抱歉~

我的需求以上都能解决，下面这条我很中意，很简短。唯一不足就是 ,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

[geniuswjt]

用master..spt_values的type='p'的，有2048条[Quote=引用 9 楼 z307755214 的回复:]

昨天发完帖，就开会去了。
抱歉~

我的需求以上都能解决，下面这条我很中意，很简短。唯一不足就是 ,master.sys.syslanguages langid 只有33条，如果生成2008-01 到现在的额月份就不行了。
SELECT
ID,
PERIOD=CONVERT(VARCHAR(7),DATEADD(m,b.langid,a.PERIOD+'-01'),120),
……
[/Quote]

[gw6328]

[Quote=引用 8 楼 qqhrswb 的回复:]

我追加个问题，如上题，如何能在取出每月26日为分界线的数据呢？
[/Quote]


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
*/