SQL编程~高手来帮忙啊。。。
目前有A、B两个表
A表中有如下字段：日期（以天为单位）；a ；b ；c ；d ；
B表中有如下字段：日期；x；y；z；n；ax；bx；
下面要做计算：
1.当日的b - 9天中数值最小的d,结果写入B表中的ax；
2.9天中数值最大的c - 9天中数值最小的d；结果写入B表中的bx；
3.AX /BX*100；计算所得到的结果写入B表中的n；
4.2/3×前一日x值+1/3×当日n ：计算结果写入B表中的x；
5.2/3×前一日y值+1/3×当日的x植；计算结果写入B表中的y；
6.注：若无前一日x值与y值，则可以分别用50代替。
7.3*当日x值-2*当日y值；计算结果写入B表中的z；
请大虾帮忙解决~谢谢啦~
[解决办法]
-- 第一步 更新 ax, bxinsert into tbB(日期, ax, bx)select t1.日期 ,ax = (t1.b - (select min(t2.d) from tbA t2 where t2.日期 between t1.日期 - 8 and t1.日期 ) ) , bx = (select (max(t3.c) - min(d)) from tbA t3 where t3.日期 between t1.日期 - 8 and t1.日期)from tbA t1;-- 第二步 更新 nupdate tbBset n = AX / BX * 100where BX <> 0;-- 第三步 更新 x, yupdate tbBset x = 50, y = 50where 日期 = (select min(日期) from tbB);-- 第四步 更新 x, yupdate t3set t3.x = t4.Wx, t3.y = t4.Wyfrom tbB t3join ( select t1.日期 ,(t2.x*2/3 - t1.n/3) as Wx ,(t2.y*2/3 - (t2.x*2/3 - t1.n/3)/3) as Wy from tbB t1 --当日 join tbB t2 --前一日 on t1.日期 = t2.日期 + 1 ) t4on t3.日期 = t4.日期;-- 第五步 更新z；update tbBset z = 3*x - 2*y;
[解决办法]
你的"9天中"我的理解是当前日之前的九天（比如今天是8月9号，指的是8月1号至9号这9天），结果计算出来有负数，不知是否符合你的要求？
如果不满足要求，请举例说明“九天中”的具体含义？
IF OBJECT_ID('tbA') IS NOT NULL DROP TABLE tbA;IF OBJECT_ID('tbB') IS NOT NULL DROP TABLE tbB;GOCREATE TABLE tbA( [日期] datetime, a numeric(8,2), b numeric(8,2), c numeric(8,2),d numeric(8,2));CREATE TABLE tbB( [日期] datetime, x numeric(8,2), y numeric(8,2), z numeric(8,2),n numeric(8,2), ax numeric(8,2), bx numeric(8,2));GO--x；y；z；n；ax；bx；insert into tbA(日期, a, b, c, d)select '2006-11-21', 2013.07, 2037.74, 1988.93, 2037.55 Union allselect '2006-11-22', 2031.3, 2062.8, 2010.79, 2041.35 Union allselect '2006-11-23', 2045.08, 2071.24, 2042.15, 2062.36 Union allselect '2006-11-24', 2051.47, 2057.91, 2022.63, 2050.81 Union allselect '2006-11-27', 2042.19, 2049.54, 2022.38, 2047.28 Union allselect '2006-11-28', 2042.38, 2051.49, 2016.47, 2038.72 Union allselect '2006-11-29', 2005.99, 2059.73, 1992.54, 2054.09 Union allselect '2006-11-30', 2058.77, 2102.06, 2058.77, 2099.29 Union allselect '2006-12-01', 2106.29, 2112.58, 2087.6, 2102.05 Union allselect '2006-12-04', 2103.82, 2164.58, 2100.56, 2161.65 Union allselect '2006-12-05', 2167.36, 2195.53, 2159.32, 2173.28 Union allselect '2006-12-06', 2175.38, 2192.9, 2097.42, 2156.6 Union allselect '2006-12-07', 2152.59, 2206.51, 2145.79, 2156.75 Union allselect '2006-12-08', 2133.35, 2164.02, 2090.4, 2093.64 Union allselect '2006-12-11', 2085.06, 2181.16, 2084.27, 2180.5 Union allselect '2006-12-12', 2189.69, 2228.64, 2180.37, 2218.95 Union allselect '2006-12-13', 2224.43, 2244.41, 2195.64, 2223.46 Union allselect '2006-12-14', 2226, 2250.32, 2212.91, 2249.11 Union allselect '2006-12-15', 2255.24, 2275.49, 2241.2, 2273.91 Union allselect '2006-12-18', 2277.28, 2335.23, 2277.28, 2332.43 Union allselect '2006-12-19', 2342.18, 2381.63, 2315.99, 2364.18 Union allselect '2006-12-20', 2356.26, 2381.13, 2332.93, 2373.21 Union allselect '2006-12-21', 2372.46, 2386.82, 2339.91, 2342.94 Union allselect '2006-12-22', 2334.27, 2366.69, 2321.92, 2343.67 Union allselect '2006-12-25', 2350.13, 2452.43, 2350.13, 2435.76 Union allselect '2006-12-26', 2437.85, 2505.7, 2424.94, 2479.73 Union allselect '2006-12-27', 2478.06, 2538.66, 2461.07, 2536.39 Union allselect '2006-12-28', 2538.95, 2611.79, 2530.33, 2567.59 Union allselect '2006-12-29', 2585.42, 2698.9, 2585.42, 2675.47;--select * from tbA;/*select *from tbA t1 left join tbA t2 on t1.日期 = t2.日期 + 1;*/select * from tbB;-- 第一步 更新 ax, bxinsert into tbB(日期, ax, bx)select t1.日期 ,ax = (t1.b - (select min(t2.d) from tbA t2 where t2.日期 between t1.日期 - 8 and t1.日期 ) ) , bx = (select (max(t3.c) - min(t3.d)) from tbA t3 where t3.日期 between t1.日期 - 8 and t1.日期)from tbA t1;-- 第二步 更新 nupdate tbBset n = AX / BX * 100where BX <> 0;-- 第三步 更新 无前一日x值与y值，用50代替 x, y的数据update t1set t1.x =50, t1.y =50from tbB t1 --当日 left join tbB t2 --前一日 on t1.日期 = t2.日期 + 1where t2.日期 is null-- 第四步 更新 x, y-- 2/3×前一日x值+1/3×当日n ：计算结果写入B表中的x； -- 2/3×前一日y值+1/3×当日的x植；计算结果写入B表中的y；while Exists(select 1 from tbB where x is Null)begin update t3 set t3.x = t4.Wx, t3.y = t4.Wy from tbB t3 join ( select t1.日期 ,Wx = (t2.x*2/3 - t1.n/3) ,Wy = (t2.y*2/3 - (t2.x*2/3 - t1.n/3)/3) from tbB t1 --当日 join tbB t2 --前一日 on t1.日期 = t2.日期 + 1 where t1.x is null and t2.x is not null ) t4 on t3.日期 = t4.日期;end;-- 第五步 更新z；update tbBset z = 3*x - 2*y;select * from tbB;/*日期 x y z n ax bx----------------------- --------- ---------- --------- ---------- ---------- --------2006-11-21 00:00:00.000 50.00 50.00 50.00 -0.39 0.19 -48.622006-11-22 00:00:00.000 64.79 11.74 170.89 -94.36 25.25 -26.762006-11-23 00:00:00.000 -200.94 74.81 -752.44 732.39 33.69 4.602006-11-24 00:00:00.000 -281.50 143.71 -1131.92 442.61 20.36 4.602006-11-27 00:00:00.000 50.00 50.00 50.00 260.65 11.99 4.602006-11-28 00:00:00.000 -67.68 55.89 -314.82 303.04 13.94 4.602006-11-29 00:00:00.000 -205.84 105.87 -829.26 482.17 22.18 4.602006-11-30 00:00:00.000 -242.53 151.42 -1030.43 315.91 63.34 20.052006-12-01 00:00:00.000 -212.05 171.63 -979.41 151.10 73.86 48.882006-12-04 00:00:00.000 50.00 50.00 50.00 203.53 125.86 61.842006-12-05 00:00:00.000 -10.01 36.67 -103.37 130.02 156.81 120.602006-12-06 00:00:00.000 -49.29 40.88 -229.63 127.84 154.18 120.602006-12-07 00:00:00.000 -81.14 54.30 -352.02 144.84 152.42 105.232006-12-08 00:00:00.000 -89.81 66.14 -401.71 107.16 70.38 65.682006-12-11 00:00:00.000 50.00 50.00 50.00 133.25 87.52 65.682006-12-12 00:00:00.000 -18.55 39.52 -134.69 155.66 135.00 86.732006-12-13 00:00:00.000 -61.64 46.89 -278.70 147.81 150.77 102.002006-12-14 00:00:00.000 -84.88 59.55 -373.74 131.37 156.68 119.272006-12-15 00:00:00.000 -97.67 72.26 -437.53 123.24 181.85 147.562006-12-18 00:00:00.000 50.00 50.00 50.00 159.88 154.73 96.782006-12-19 00:00:00.000 -16.15 38.72 -125.89 148.45 201.13 135.492006-12-20 00:00:00.000 -58.20 45.21 -265.02 142.29 162.18 113.982006-12-21 00:00:00.000 -85.56 58.66 -374.00 140.28 163.36 116.452006-12-22 00:00:00.000 -100.20 72.51 -445.62 129.49 117.58 90.802006-12-25 00:00:00.000 50.00 50.00 50.00 677.97 120.00 17.702006-12-26 00:00:00.000 -29.10 43.03 -173.36 187.30 173.27 92.512006-12-27 00:00:00.000 -74.63 53.56 -331.01 165.68 195.72 118.132006-12-28 00:00:00.000 -97.58 68.23 -429.20 143.47 268.85 187.392006-12-29 00:00:00.000 -113.99 83.48 -508.93 146.80 355.96 242.48*/