日期:2014-05-18  浏览次数:20667 次

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;

请大虾帮忙解决~谢谢啦~

------解决方案--------------------
SQL code

-- 第一步 更新 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天),结果计算出来有负数,不知是否符合你的要求?
如果不满足要求,请举例说明“九天中”的具体含义?
SQL code

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