declare @i datetime,@j datetime set @i='2009-01-01' set @j='2010-01-01' select number/2,avg(数据字段) from spt_values A,你的表 B where dateadd(dd,number,@i)=数据字段 and type='p' and dateadd(dd,number,@i)<=@j group by number/2
DECLARE @TB TABLE(COL DATETIME, COL2 FLOAT) INSERT @TB SELECT '2009-01-01', 2.0 UNION ALL SELECT '2009-01-02', 3.0 UNION ALL SELECT '2009-01-03', 4.0 UNION ALL SELECT '2009-01-04', 5.0 UNION ALL SELECT '2009-01-05', 6.0 SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM @TB ORDER BY COLDECLARE @GRP INT SET @GRP=2SELECT AVG(COL2) AS SCORE FROM # GROUP BY (ID-1)/@GRPDROP TABLE # /* SCORE ----------------------------------------------------- 2.5 4.5 6.0 */
IF NOT OBJECT_ID('TB') IS NULL DROP TABLE TB GO CREATE TABLE TB([ADATE] DATETIME,[NUM] INT) INSERT TB SELECT '2009-01-01',10 UNION ALL SELECT '2009-01-02',70 UNION ALL SELECT '2009-01-03',40 UNION ALL SELECT '2009-01-04',30 UNION ALL SELECT '2009-01-05',20 UNION ALL SELECT '2009-01-06',80 UNION ALL SELECT '2009-01-07',70 UNION ALL SELECT '2009-01-08',60 UNION ALL SELECT '2009-01-09',50 UNION ALL SELECT '2009-01-10',40 UNION ALL SELECT '2009-01-11',20 UNION ALL SELECT '2009-01-12',30 GO SELECT BDATE=CONVERT(VARCHAR(10),MIN(ADATE),120)+'~'+CONVERT(VARCHAR(10),MAX(ADATE),120), AVGNUM=AVG(NUM) FROM TB GROUP BY DATEDIFF(DAY,'2009-01-01',ADATE)/2 /* BDATE AVGNUM --------------------- ----------- 2009-01-01~2009-01-02 40 2009-01-03~2009-01-04 35 2009-01-05~2009-01-06 50 2009-01-07~2009-01-08 65 2009-01-09~2009-01-10 45 2009-01-11~2009-01-12 25(所影响的行数为 6 行) */
借用8楼的数据 IF NOT OBJECT_ID('TB') IS NULL DROP TABLE TB GO CREATE TABLE TB([ADATE] DATETIME,[NUM] INT) INSERT TB SELECT '2009-01-01',10 UNION ALL SELECT '2009-01-04',30 UNION ALL SELECT '2009-01-05',20 UNION ALL SELECT '2009-01-06',80 UNION ALL SELECT '2009-01-07',70 UNION ALL SELECT '2009-01-08',60 UNION ALL SELECT '2009-01-09',50 UNION ALL SELECT '2009-01-10',40 UNION ALL SELECT '2009-01-11',20 UNION ALL SELECT '2009-01-12',30 GOselect ADATE=CONVERT(VARCHAR(10),MIN(ADATE),120)+'~'+CONVERT(VARCHAR(10),MAX(ADATE),120), NUM=avg(isnull(NUM,0)) from spt_values A left join TB B on ADATE=dateadd(dd,number,(select min(ADATE) from TB)) where type='p' and dateadd(dd,number,(select min(ADATE) from TB))<=(select max(ADATE) from TB) group by number/2/* ADATE NUM --------------------- ----------- 2009-01-01~2009-01-01 5 2009-01-04~2009-01-04 15 2009-01-05~2009-01-06 50 2009-01-07~2009-01-08 65 2009-01-09~2009-01-10 45 2009-01-11~2009-01-12 25(所影响的行数为 6 行)警告: 聚合或其它 SET 操作消除了空值。 */drop table TB
set @i='2009-01-01'
set @j='2010-01-01'
select number/2,avg(数据字段)
from spt_values A,你的表 B
where dateadd(dd,number,@i)=数据字段 and type='p' and dateadd(dd,number,@i)<=@j
group by number/2
n单位不一定是天,可能是月,时,分,甚至是秒都有可能的
这个N也不是属于一个字段的
INSERT @TB
SELECT '2009-01-01', 2.0 UNION ALL
SELECT '2009-01-02', 3.0 UNION ALL
SELECT '2009-01-03', 4.0 UNION ALL
SELECT '2009-01-04', 5.0 UNION ALL
SELECT '2009-01-05', 6.0 SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM @TB ORDER BY COLDECLARE @GRP INT
SET @GRP=2SELECT AVG(COL2) AS SCORE
FROM #
GROUP BY (ID-1)/@GRPDROP TABLE #
/*
SCORE
-----------------------------------------------------
2.5
4.5
6.0
*/
GO
CREATE TABLE TB([ADATE] DATETIME,[NUM] INT)
INSERT TB
SELECT '2009-01-01',10 UNION ALL
SELECT '2009-01-02',70 UNION ALL
SELECT '2009-01-03',40 UNION ALL
SELECT '2009-01-04',30 UNION ALL
SELECT '2009-01-05',20 UNION ALL
SELECT '2009-01-06',80 UNION ALL
SELECT '2009-01-07',70 UNION ALL
SELECT '2009-01-08',60 UNION ALL
SELECT '2009-01-09',50 UNION ALL
SELECT '2009-01-10',40 UNION ALL
SELECT '2009-01-11',20 UNION ALL
SELECT '2009-01-12',30
GO
SELECT BDATE=CONVERT(VARCHAR(10),MIN(ADATE),120)+'~'+CONVERT(VARCHAR(10),MAX(ADATE),120),
AVGNUM=AVG(NUM) FROM TB
GROUP BY DATEDIFF(DAY,'2009-01-01',ADATE)/2
/*
BDATE AVGNUM
--------------------- -----------
2009-01-01~2009-01-02 40
2009-01-03~2009-01-04 35
2009-01-05~2009-01-06 50
2009-01-07~2009-01-08 65
2009-01-09~2009-01-10 45
2009-01-11~2009-01-12 25(所影响的行数为 6 行)
*/
IF NOT OBJECT_ID('TB') IS NULL DROP TABLE TB
GO
CREATE TABLE TB([ADATE] DATETIME,[NUM] INT)
INSERT TB
SELECT '2009-01-01',10 UNION ALL
SELECT '2009-01-04',30 UNION ALL
SELECT '2009-01-05',20 UNION ALL
SELECT '2009-01-06',80 UNION ALL
SELECT '2009-01-07',70 UNION ALL
SELECT '2009-01-08',60 UNION ALL
SELECT '2009-01-09',50 UNION ALL
SELECT '2009-01-10',40 UNION ALL
SELECT '2009-01-11',20 UNION ALL
SELECT '2009-01-12',30
GOselect ADATE=CONVERT(VARCHAR(10),MIN(ADATE),120)+'~'+CONVERT(VARCHAR(10),MAX(ADATE),120),
NUM=avg(isnull(NUM,0))
from spt_values A left join TB B
on ADATE=dateadd(dd,number,(select min(ADATE) from TB))
where type='p' and dateadd(dd,number,(select min(ADATE) from TB))<=(select max(ADATE) from TB)
group by number/2/*
ADATE NUM
--------------------- -----------
2009-01-01~2009-01-01 5
2009-01-04~2009-01-04 15
2009-01-05~2009-01-06 50
2009-01-07~2009-01-08 65
2009-01-09~2009-01-10 45
2009-01-11~2009-01-12 25(所影响的行数为 6 行)警告: 聚合或其它 SET 操作消除了空值。
*/drop table TB