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

雷肿么了 2011-11-09 01:19:31


--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
...全文
151 11 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
gw6328 2011-11-10
  • 打赏
  • 举报
回复
[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
*/
geniuswjt 2011-11-10
  • 打赏
  • 举报
回复
用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]
雷肿么了 2011-11-10
  • 打赏
  • 举报
回复
昨天发完帖,就开会去了。
抱歉~

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

QQHRSWB 2011-11-09
  • 打赏
  • 举报
回复
我追加个问题,如上题,如何能在取出每月26日为分界线的数据呢?
--小F-- 2011-11-09
  • 打赏
  • 举报
回复
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 行受影响)
*/
中国风 2011-11-09
  • 打赏
  • 举报
回复
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-- 2011-11-09
  • 打赏
  • 举报
回复
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 行受影响)
*/
jwdream2008 2011-11-09
  • 打赏
  • 举报
回复
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())
-晴天 2011-11-09
  • 打赏
  • 举报
回复
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

中国风 2011-11-09
  • 打赏
  • 举报
回复
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

中国风 2011-11-09
  • 打赏
  • 举报
回复
11月的數據在那?

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧