数据库更新操作

u010341022 2015-08-01 03:02:17
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的最大的一个
...全文
84 7 点赞 打赏 收藏 举报
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
u010341022 2015-08-04
引用 5 楼 lzw_0736 的回复:

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
谢谢各位了!已经解决了
  • 打赏
  • 举报
回复
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>=5
codeid      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 行受影响)
这个效率应该很好的,可以在codeid上添加索引
  • 打赏
  • 举报
回复
sckdcs 2015-08-01
简单语句不行,用游标写成程序试试,肯定可行。
  • 打赏
  • 举报
回复
u010341022 2015-08-01
别沉啊 ,,,,,
  • 打赏
  • 举报
回复
u010341022 2015-08-01
大概五百万的数据,怎么更新啊,大神们
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-08-01 03:02
社区公告
暂无公告