日期:2014-05-18 浏览次数:20734 次
-- 第一步 更新 ax, bx insert 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; -- 第二步 更新 n update tbB set n = AX / BX * 100 where BX <> 0; -- 第三步 更新 x, y update tbB set x = 50, y = 50 where 日期 = (select min(日期) from tbB); -- 第四步 更新 x, y update t3 set t3.x = t4.Wx, t3.y = t4.Wy from tbB t3 join ( 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 ) t4 on t3.日期 = t4.日期; -- 第五步 更新z; update tbB set 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; GO CREATE 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 all select '2006-11-22', 2031.3, 2062.8, 2010.79, 2041.35 Union all select '2006-11-23', 2045.08, 2071.24, 2042.15, 2062.36 Union all select '2006-11-24', 2051.47, 2057.91, 2022.63, 2050.81 Union all select '2006-11-27', 2042.19, 2049.54, 2022.38, 2047.28 Union all select '2006-11-28', 2042.38, 2051.49, 2016.47, 2038.72 Union all select '2006-11-29', 2005.99, 2059.73, 1992.54, 2054.09 Union all select '2006-11-30', 2058.77, 2102.06, 2058.77, 2099.29 Union all select '2006-12-01', 2106.29, 2112.58, 2087.6, 2102.05 Union all select '2006-12-04', 2103.82, 2164.58, 2100.56, 2161.65 Union all select '2006-12-05', 2167.36, 2195.53, 2159.32, 2173.28 Union all select '2006-12-06', 2175.38, 2192.9, 2097.42, 2156.6 Union all select '2006-12-07', 2152.59, 2206.51, 2145.79, 2156.75 Union all select '2006-12-08', 2133.35, 2164.02, 2090.4, 2093.64 Union all select '2006-12-11', 2085.06, 2181.16, 2084.27, 2180.5 Union all select '2006-12-12', 2189.69, 2228.64, 2180.37, 2218.95 Union all select '2006-12-13', 2224.43, 2244.41, 2195.64, 2223.46 Union all select '2006-12-14', 2226, 2250.32, 2212.91, 2249.11 Union all select '2006-12-15', 2255.24, 2275.49, 2241.2, 2273.91 Union all select '2006-12-18', 2277.28, 2335.23, 2277.28, 2332.43 Union all select '2006-12-19', 2342.18, 2381.63, 2315.99, 2364.18 Union all select '2006-12-20', 2356.26, 2381.13, 2332.93, 2373.21 Union all select '2006-12-21', 2372.46, 2386.82, 2339.91, 2342.94 Union all select '2006-12-22', 2334.27, 2366.69, 2321.92, 2343.67 Union all select '2006-12-25', 2350.13, 2452.43, 2350.13, 2435.76 Union all select '2006-12-26', 2437.85, 2505.7, 2424.94, 2479.73 Union all select '2006-12-27', 2478.06, 2538.66, 2461.07, 2536.39 Union all select '2006-12-28', 2538.95, 2611.79, 2530.33, 2567.59 Union all select '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, bx insert in