110,539
社区成员
发帖
与我相关
我的任务
分享
List<int> data = Enumerable.Range(1, 29).ToList();
List<int> result = new List<int>();
for (int i = 0; i < data.Count && result.Count < 24; i++)
{
result.Add(data.Skip(i).Take(6).Sum());
}
-------------------- 构建测试表和测试数据 BEGIN -----------------------
--之所以长是因为需要构建一批随机记录,如果你有现成的表可以省去这些
USE tempdb
GO
--1. 创建随机视图 view_rand
if object_id('view_rand') is not null
begin
drop view view_rand
end
go
create view view_rand
as
select rand() as [r]
GO
--创建日期(天)的随机函数
if object_id('dbo.Fun_GetRand') is not null
begin
drop function dbo.Fun_GetRand
end
go
CREATE FUNCTION dbo.Fun_GetRand
(
@max INT
)
returns int
as
begin
declare @r int
select @r =CAST([r] * 100 AS int) from view_rand
WHILE @r>@max
BEGIN
select @r =CAST([r] * 100 AS int) from view_rand
END
return @r
end
go
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
CREATE TABLE t(id INT IDENTITY(1,1) PRIMARY KEY,c1 INT NOT NULL)
INSERT INTO t(c1)
SELECT dbo.Fun_GetRand(29)
FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number >=1
SELECT * FROM t WHERE id<=29
/*
id c1
1 10
2 12
3 2
4 24
5 15
6 26
7 17
8 11
9 23
10 16
11 28
12 28
13 28
14 4
15 23
16 11
17 6
18 11
19 2
20 14
21 19
22 11
23 24
24 28
25 5
26 1
27 26
28 22
29 4
*/
-------------------- 构建测试表和测试数据 END -----------------------
;WITH cte AS (
SELECT ROW_NUMBER()OVER(ORDER BY id)AS rid, * from t
),cte2 AS (
SELECT * FROM cte WHERE rid<=29
),cte3 AS (
SELECT a.groupId,b.rid,b.c1
FROM (SELECT sv.number AS groupId FROM [master].dbo.spt_values AS sv WHERE sv.number BETWEEN 1 AND 24)AS a
LEFT JOIN (SELECT * FROM cte2) AS b ON b.rid BETWEEN a.groupId AND a.groupId + 5
GROUP BY a.groupId,b.rid,b.c1
)
SELECT t.groupId,AVG(c1) AS avgResult
FROM cte3 AS t GROUP BY t.groupId
/*
groupId avgResult
1 14
2 16
3 15
4 19
5 18
6 20
7 20
8 22
9 21
10 21
11 20
12 16
13 13
14 9
15 11
16 10
17 10
18 13
19 16
20 16
21 14
22 15
23 17
24 14
*/
-------------------- 构建测试表和测试数据 BEGIN -----------------------
--之所以长是因为需要构建一批随机记录,如果你有现成的表可以省去这些
USE tempdb
GO
--1. 创建随机视图 view_rand
if object_id('view_rand') is not null
begin
drop view view_rand
end
go
create view view_rand
as
select rand() as [r]
GO
--创建日期(天)的随机函数
if object_id('dbo.Fun_GetRand') is not null
begin
drop function dbo.Fun_GetRand
end
go
CREATE FUNCTION dbo.Fun_GetRand
(
@max INT
)
returns int
as
begin
declare @r int
select @r =CAST([r] * 100 AS int) from view_rand
WHILE @r>@max
BEGIN
select @r =CAST([r] * 100 AS int) from view_rand
END
return @r
end
go
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
CREATE TABLE t(id INT IDENTITY(1,1) PRIMARY KEY,c1 INT NOT NULL)
INSERT INTO t(c1)
SELECT dbo.Fun_GetRand(29)
FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number >=1
SELECT * FROM t WHERE id<=29
/*
id c1
1 27
2 13
3 5
4 29
5 26
6 24
7 8
8 26
9 1
10 22
11 8
12 15
13 8
14 27
15 3
16 3
17 13
18 24
19 16
20 12
21 15
22 12
23 14
24 17
25 7
26 27
27 21
28 19
29 27
*/
-------------------- 构建测试表和测试数据 END -----------------------
;WITH cte AS (
SELECT ROW_NUMBER()OVER(ORDER BY id)AS rid, * from t
),cte2 AS (
SELECT * FROM cte WHERE rid<=29
),cte3 AS (
SELECT a.groupId,b.rid,b.c1
FROM (SELECT sv.number AS groupId FROM [master].dbo.spt_values AS sv WHERE sv.number BETWEEN 1 AND 24)AS a
LEFT JOIN (SELECT * FROM cte2) AS b ON b.rid BETWEEN a.groupId AND a.groupId + 5
GROUP BY a.groupId,b.rid,b.c1
)
SELECT t.groupId,SUM(c1) AS sumResult
FROM cte3 AS t GROUP BY t.groupId
/*
groupId sumResult
1 124
2 105
3 118
4 114
5 107
6 89
7 80
8 80
9 81
10 83
11 64
12 69
13 78
14 86
15 71
16 83
17 92
18 93
19 86
20 77
21 92
22 98
23 105
24 118
*/