表A1
FDate(datetime型) FQty(decimal型,反正算加法)
第一列是几年的数据,有很多条(上十万),日期和数量都可能重复,我要求是输出每个月的25号(中间连续的),对应的数量是到前一个月25日再往前一年内的数量和。下面给具体例子2009-12-23 5
2010-01-20 00:00:00 1
2010-01-25 2
2010-01-26 。 3
这里省略多行(数量就算0吧)
2010-02-27 。 4
2010-03-25 。 5
2010-12-26. 100
再次省略
2011-01-23 。 6
2011-01-25 。 7
2011-02-03 。 8
2011-03-03 。 9
2011-03-25 。 10
2011-03-26(或者2011-04-01) 11后面就不写啦
输出的是
date qty
2009-12-25 0
2010-01-25 5 (这里算的是2008-12-26到2009-12-25)
2010-02-25 5+1+2=3(只输出8)
2010-03-25 5+1+2+3=11(只输出11)
2010-04-25 11+4+5=20 (这里算的是2009-03-26到2010-03-25)
每个月都不能少05-25,06-25等等
2010-12-25 5+1+2+3+4+5=20 (2009-11-26到2010-11-25)
2011-01-25 1+2+3+4+5=15 (2009-12-26到2010-12-25)
2011-02-25 3+4+5+100+6+7=125 (2010-01-26到2011-01-25)
2011-03-25 4+5+100+6+7+8=130 (2010-02-26到2011-02-25)
2011-04-25 100+6+7+8+9+10=140 (2010-03-26到2011-03-25)
2011-05-25 100+6+7+8+9+10+11=151 (2010-04-26到2011-04-25)
(如果2011-03-26(或者2011-04-01)是最后一条,就写到这里就好了,实际上有3年的数据,50多万条)
sql ,server,select
FDate(datetime型) FQty(decimal型,反正算加法)
第一列是几年的数据,有很多条(上十万),日期和数量都可能重复,我要求是输出每个月的25号(中间连续的),对应的数量是到前一个月25日再往前一年内的数量和。下面给具体例子2009-12-23 5
2010-01-20 00:00:00 1
2010-01-25 2
2010-01-26 。 3
这里省略多行(数量就算0吧)
2010-02-27 。 4
2010-03-25 。 5
2010-12-26. 100
再次省略
2011-01-23 。 6
2011-01-25 。 7
2011-02-03 。 8
2011-03-03 。 9
2011-03-25 。 10
2011-03-26(或者2011-04-01) 11后面就不写啦
输出的是
date qty
2009-12-25 0
2010-01-25 5 (这里算的是2008-12-26到2009-12-25)
2010-02-25 5+1+2=3(只输出8)
2010-03-25 5+1+2+3=11(只输出11)
2010-04-25 11+4+5=20 (这里算的是2009-03-26到2010-03-25)
每个月都不能少05-25,06-25等等
2010-12-25 5+1+2+3+4+5=20 (2009-11-26到2010-11-25)
2011-01-25 1+2+3+4+5=15 (2009-12-26到2010-12-25)
2011-02-25 3+4+5+100+6+7=125 (2010-01-26到2011-01-25)
2011-03-25 4+5+100+6+7+8=130 (2010-02-26到2011-02-25)
2011-04-25 100+6+7+8+9+10=140 (2010-03-26到2011-03-25)
2011-05-25 100+6+7+8+9+10+11=151 (2010-04-26到2011-04-25)
(如果2011-03-26(或者2011-04-01)是最后一条,就写到这里就好了,实际上有3年的数据,50多万条)
sql ,server,select
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L4)
SELECT cast(40542 + 3 * (n - 1) as datetime) as dt,1 as Qty
INTO #TA
FROM Nums
WHERE n <= 500
--滑动聚合
;WITH CET AS
(
select case when datepart(DD,dt)>=25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end as YM, SUM(Qty) SumQty
from #TA
group by case when datepart(DD,dt)>=25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end
)
SELECT O1.YM, SUM(O2.SumQty) AS totalqty
FROM CET AS O1
JOIN CET AS O2
ON O2.YM <= O1.YM and o2.YM>dateadd(month,-12,o1.YM)
GROUP BY O1.YM
ORDER BY O1.YM;
----------------------------------------------------------------
-- Author :谭磊
-- Date :2013-01-11 18:46:41
-- Version:
-- Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
-- Jun 28 2012 08:36:30
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)
--
----------------------------------------------------------------
--> 测试数据:#TA
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go
create table #TA([DT] datetime,[Qty] int)
insert #TA
select '2009-12-23',5 union all
select '2010-01-20',1 union all
select '2010-01-25',2 union all
select '2010-01-26',3 union all
select '2010-02-27',4 union all
select '2010-03-25',5 union all
select '2010-12-26',100 union all
select '2011-01-23',6 union all
select '2011-01-25',7 union all
select '2011-02-03',8 union all
select '2011-03-03',9 union all
select '2011-03-25',10 union all
select '2011-03-26',11
--------------开始查询----------------------------滑动聚合
;WITH CET AS
(
select case when datepart(DD,dt)>=25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end as YM, SUM(Qty) SumQty
from #TA
group by case when datepart(DD,dt)>=25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end
)
SELECT O1.YM, SUM(O2.SumQty) AS totalqty
FROM CET AS O1
JOIN CET AS O2
ON O2.YM <= O1.YM and o2.YM>dateadd(month,-12,o1.YM)
GROUP BY O1.YM
ORDER BY O1.YM;
----------------结果----------------------------
/* YM totalqty
---------- -----------
2010-01-25 5
2010-02-25 6
2010-03-25 11
2010-04-25 15
2010-05-25 20
2011-02-25 120
2011-03-25 130
2011-04-25 135
2011-05-25 151(9 行受影响)*/
--滑动聚合
;WITH CET AS
(
select case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end as YM, SUM(Qty) SumQty
from #TA
group by case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end
)
SELECT O1.YM, SUM(O2.SumQty) AS totalqty
FROM CET AS O1
JOIN CET AS O2
ON O2.YM <= O1.YM and o2.YM>dateadd(month,-12,o1.YM)
GROUP BY O1.YM
ORDER BY O1.YM;
----------------结果----------------------------
/*
YM totalqty
---------- -----------
2010-01-25 5
2010-02-25 8
2010-03-25 11
2010-04-25 20
2011-02-25 125
2011-03-25 130
2011-04-25 140
2011-05-25 151(8 行受影响)*/
with cet as是什么
(
select case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end as YM, SUM(Qty)这里求的是什么累计 SumQty
from #TA
group by case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end
)
上面的 with net as 可以写在最后么,作为一个表。
(
select case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end as YM, SUM(Qty)这里求的是什么累计 SumQty
from #TA
group by case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end
) NET
NET,我把后面的NET表换成这段话。你说不可以?是么? (问题1。1)
在2008新建查询里面这样写没错么?(1.2)(这个我会去试试的,没这么写过)你with NET as括号里面先求的是日期,是一个月的QTY? (2.1)
这一个月是指,比如2011-03-25 求的是2011-01-26到2011-02-25的和么?(2.2)
下面一段在求的12个月吧?(2.3)