22,207
社区成员
发帖
与我相关
我的任务
分享数据库更新操作
codeid High FiveHigh
1 35.600 NULL
2 38.190 NULL
3 41.980 NULL
4 40.150 NULL
5 42.870 NULL
6 40.880 NULL
7 41.450 NULL
8 43.880 NULL
9 42.890 NULL
10 47.050 NULL
11 47.860 NULL
12 48.040 NULL
13 45.500 NULL
14 45.300 NULL
15 44.600 NULL
16 42.190 NULL
17 41.990 NULL
18 43.800 NULL
19 43.500 NULL
20 41.330 NULL
21 42.100 NULL
22 41.850 NULL
23 41.570 NULL
24 41.990 NULL
25 41.200 NULL
26 44.420 NULL
27 44.270 NULL
28 44.380 NULL
29 42.430 NULL
30 41.690 NULL
更新FiveHigh从第五个开始，就是High为id1-5的最大的一个，第六个就是High为id2-6的最大的一个，第七个就是High为id3-7的最大的一个
...全文
请发表友善的回复…
发表回复
u010341022 2015-08-04
- 打赏
- 举报
谢谢各位了!已经解决了
lzw_0736 2015-08-01
- 打赏
- 举报
--在 High上也添加索引
lzw_0736 2015-08-01
- 打赏
- 举报
CREATE TABLE #TEST
(
codeid INT,
High NUMERIC(10,3),
FiveHigh NUMERIC(10,3)
)
--在codeid上添加索引
INSERT INTO #TEST
SELECT 1,35.600,NULL UNION ALL
SELECT 2,38.190,NULL UNION ALL
SELECT 3,41.980,NULL UNION ALL
SELECT 4,40.150,NULL UNION ALL
SELECT 5,42.870,NULL UNION ALL
SELECT 6,40.880,NULL UNION ALL
SELECT 7,41.450,NULL UNION ALL
SELECT 8,43.880,NULL UNION ALL
SELECT 9,42.890,NULL UNION ALL
SELECT 10,47.050,NULL UNION ALL
SELECT 11,47.860,NULL UNION ALL
SELECT 12,48.040,NULL UNION ALL
SELECT 13,45.500,NULL UNION ALL
SELECT 14,45.300,NULL UNION ALL
SELECT 15,44.600,NULL UNION ALL
SELECT 16,42.190,NULL UNION ALL
SELECT 17,41.990,NULL UNION ALL
SELECT 18,43.800,NULL UNION ALL
SELECT 19,43.500,NULL UNION ALL
SELECT 20,41.330,NULL UNION ALL
SELECT 21,42.100,NULL UNION ALL
SELECT 22,41.850,NULL UNION ALL
SELECT 23,41.570,NULL UNION ALL
SELECT 24,41.990,NULL UNION ALL
SELECT 25,41.200,NULL UNION ALL
SELECT 26,44.420,NULL UNION ALL
SELECT 27,44.270,NULL UNION ALL
SELECT 28,44.380,NULL UNION ALL
SELECT 29,42.430,NULL UNION ALL
SELECT 30,41.690,NULL
UPDATE a SET FiveHigh=b.High
FROM #test a
CROSS APPLY
(
SELECT TOP 1 High FROM #test WHERE codeid BETWEEN a.codeid-4 AND a.codeid ORDER BY High desc
) b
WHERE a.codeid>4
许晨旭 2015-08-01
- 打赏
- 举报
CREATE TABLE TEST
(
codeid INT,
High NUMERIC(10,3),
FiveHigh NUMERIC(10,3)
)
TRUNCATE TABLE TEST
INSERT INTO TEST
SELECT 1,35.600,NULL UNION ALL
SELECT 2,38.190,NULL UNION ALL
SELECT 3,41.980,NULL UNION ALL
SELECT 4,40.150,NULL UNION ALL
SELECT 5,42.870,NULL UNION ALL
SELECT 6,40.880,NULL UNION ALL
SELECT 7,41.450,NULL UNION ALL
SELECT 8,43.880,NULL UNION ALL
SELECT 9,42.890,NULL UNION ALL
SELECT 10,47.050,NULL UNION ALL
SELECT 11,47.860,NULL UNION ALL
SELECT 12,48.040,NULL UNION ALL
SELECT 13,45.500,NULL UNION ALL
SELECT 14,45.300,NULL UNION ALL
SELECT 15,44.600,NULL UNION ALL
SELECT 16,42.190,NULL UNION ALL
SELECT 17,41.990,NULL UNION ALL
SELECT 18,43.800,NULL UNION ALL
SELECT 19,43.500,NULL UNION ALL
SELECT 20,41.330,NULL UNION ALL
SELECT 21,42.100,NULL UNION ALL
SELECT 22,41.850,NULL UNION ALL
SELECT 23,41.570,NULL UNION ALL
SELECT 24,41.990,NULL UNION ALL
SELECT 25,41.200,NULL UNION ALL
SELECT 26,44.420,NULL UNION ALL
SELECT 27,44.270,NULL UNION ALL
SELECT 28,44.380,NULL UNION ALL
SELECT 29,42.430,NULL UNION ALL
SELECT 30,41.690,NULL
UPDATE TEST SET FiveHigh=
(select max(b.High) FROM (SELECT TOP 5 High FROM TEST a WHERE a.codeid>=TEST.codeid-4)b)
where TEST.codeid>=5codeid High FiveHigh
----------- --------------------------------------- ---------------------------------------
1 35.600 NULL
2 38.190 NULL
3 41.980 NULL
4 40.150 NULL
5 42.870 42.870
6 40.880 42.870
7 41.450 42.870
8 43.880 43.880
9 42.890 43.880
10 47.050 47.050
11 47.860 47.860
12 48.040 48.040
13 45.500 48.040
14 45.300 48.040
15 44.600 48.040
16 42.190 48.040
17 41.990 45.500
18 43.800 45.300
19 43.500 44.600
20 41.330 43.800
21 42.100 43.800
22 41.850 43.800
23 41.570 43.500
24 41.990 42.100
25 41.200 42.100
26 44.420 44.420
27 44.270 44.420
28 44.380 44.420
29 42.430 44.420
30 41.690 44.420
(30 行受影响)
sckdcs 2015-08-01
- 打赏
- 举报
简单语句不行，用游标写成程序试试，肯定可行。
u010341022 2015-08-01
- 打赏
- 举报
别沉啊 ，，，，，
u010341022 2015-08-01
- 打赏
- 举报
大概五百万的数据，怎么更新啊，大神们
