如何在水晶报告中获得3个连续结果的平均值（日期：19-05-2015）

问题描述

ID + Avg    + average 3 consecutive 
A  +  60.0  +    --             +
B  +  62.0  +    --             +
C  +  63.0  +    61.7           + <- Average of (ABC)
D  +  59.0  +    61.3           + <- Average of (BCD)
E  +  58.0  +    60.0           + <- Average of (CDE)
F  +  60.0  +    59.0           + <- Average of (DEF)
G  +  61.0  +    59.7           + <- Average of (EFG)

在我的报告中，我正在寻找这个结果（第3栏）

i有第1和第2列我不能在水晶报告中第3列（平均连续3次）。



请帮助...



提前致谢

In my report i am looking for this result (3 rd column)
i have the 1st and 2nd column i cannot make the third column(average 3 consecutive ) in crystal report.

Please help...

Thanks in advance

推荐答案

DECLARE @DATA TABLE 
(
    ID NCHAR(1) NOT NULL,
    AVARAGE FLOAT NOT NULL
)

INSERT  @DATA(ID, AVARAGE)
	SELECT  'A', 60.0
	UNION ALL
	SELECT  'B', 62.0
	UNION ALL
	SELECT  'C', 63.0
	UNION ALL
	SELECT  'D', 59.0
	UNION ALL
	SELECT  'E', 58.0
	UNION ALL
	SELECT  'F', 60.0
	UNION ALL
	SELECT  'G', 61.0

;
WITH ORDERED_TABLE AS
(
	SELECT ID, AVARAGE, ROW_NUMBER() OVER (ORDER BY ID) AS ROWNUMBER FROM @DATA
)

SELECT 
	OT_OUT.ID, 
	OT_OUT.AVARAGE,
	CASE WHEN OT_OUT.ROWNUMBER < 3
		THEN NULL
		ELSE
			(
				SELECT AVG(AVARAGE) 
    				FROM ORDERED_TABLE AS OT_IN
    				WHERE OT_IN.ROWNUMBER IN (OT_OUT.ROWNUMBER, OT_OUT.ROWNUMBER - 1, OT_OUT.ROWNUMBER - 2)
			)
	END AS CONSECUTIVE_AVARAGE
FROM ORDERED_TABLE AS OT_OUT

WITH ORDERED_TABLE AS，声明一个CTE，你在每行添加一个行号，这样你就可以说出前两行用于平均计算的那个......

第二行部分是一个基于此CTE的简单查询来进行计算...

WITH ORDERED_TABLE AS, declares a CTE where you add to every row a row-number so you can say which hare the previous two rows to use with average computation...
The second part is a simple query based on this CTE to do the computation...

这篇关于如何在水晶报告中获得3个连续结果的平均值（日期：19-05-2015）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！