想把SQL中某一列按每十行进行分组求平均值,再把所得的平均值保存在该表新建的一列中,请问怎么解决。-ZOL问答

4个回答默认排序

默认排序

按时间排序

已采纳

Val1 是某一列。
Val2 是 Val1 每十行平均值

CREATE TABLE #Goods (
ID INT PRIMARY KEY,
Val1 DECIMAL(5,2),
Val2 DECIMAL(5,2)
);

INSERT INTO #Goods (ID, Val1)
SELECT 1, 1 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 3, 3 UNION ALL
SELECT 4, 4 UNION ALL
SELECT 5, 5 UNION ALL
SELECT 6, 6 UNION ALL
SELECT 7, 7 UNION ALL
SELECT 8, 8 UNION ALL
SELECT 9, 9 UNION ALL
SELECT 10, 10 UNION ALL
SELECT 11, 11 UNION ALL
SELECT 12, 12 UNION ALL
SELECT 13, 13 UNION ALL
SELECT 14, 14 UNION ALL
SELECT 15, 15 UNION ALL
SELECT 16, 16 UNION ALL
SELECT 17, 17 UNION ALL
SELECT 18, 18 UNION ALL
SELECT 19, 19 UNION ALL
SELECT 20, 20;

With myCTE AS (
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rownum,
id,
Val1
FROM
#Goods
),
avgCTE AS (
SELECT
(rownum - 1) / 10 AS 分组,
Min(rownum) AS MinNo,
Max(rownum) AS MaxNo,
AVG(Val1) AS Val2
FROM
myCTE
GROUP BY
(rownum - 1) / 10
),
OtherCTE AS (
SELECT
id, Val1, Val2
FROM
myCTE JOIN avgCTE ON ( myCTE.rownum BETWEEN avgCTE.MinNo AND avgCTE.MaxNo)
)
UPDATE
#Goods
SET
Val2 = OtherCTE.Val2
FROM
#Goods g JOIN OtherCTE ON (g.id = OtherCTE.id)

SELECT * FROM #Goods

ID Val1 Val2
----------- --------------------------------------- ---------------------------------------
1 1.00 5.50
2 2.00 5.50
3 3.00 5.50
4 4.00 5.50
5 5.00 5.50
6 6.00 5.50
7 7.00 5.50
8 8.00 5.50
9 9.00 5.50
10 10.00 5.50
11 11.00 15.50
12 12.00 15.50
13 13.00 15.50
14 14.00 15.50
15 15.00 15.50
16 16.00 15.50
17 17.00 15.50
18 18.00 15.50
19 19.00 15.50
20 20.00 15.50

(20 行受影响)

取消评论

精品应用推荐

超值推荐

rslee

单纯的sql没那种功能的配合编程语言来

取消评论

jymoshi

with cte_t(rate)
as (select round(sum(finishedNumber)/sum(quatity),4) from Orders group by ordername)
select rate from cte_t --如果是求全部平均，则改为：select avg(rate) from cte_t

取消评论

No7soso

注：先把行号查询出来，通过行号分组，计算合计。修改条件是以行号在每组的行号的范围之内。至于显示在其他表，你自己想吧！

update 厚度表 set 平均厚度2=
(
select round(sum(平均厚度)/10)
from
(select 厚度表.*,rownum from 厚度表) t1 group by trunc(rownum/10)
) where
rownum (select max(rownum) as maxrownum from
(select 厚度表.*,rownum from 厚度表) t1 group by trunc(rownum/10)
)) and =
(select min(rownum) as minrownum from
(select 厚度表.*,rownum from 厚度表) t1 group by trunc(rownum/10)
))

取消评论

4个回答默认排序

默认排序

按时间排序

精品应用推荐

最新回答更多>

产品比一比查看详情 >>

相关问答

热门数码相机排行

举报

举报成功

私信

删除

数码爱好者必备神器

中关村在线客户端

4个回答 默认排序 默认排序 按时间排序

精品应用推荐

最新回答 更多>

产品比一比查看详情 >>

相关问答

热门数码相机排行

举报

举报成功

私信

删除

4个回答默认排序

默认排序

按时间排序

最新回答更多>