mysql - 从两个不同的表计算百分比

Question

我有两个表，我想获取百分比值并将百分比更新到表中。假设我们有两个这样的表，

表一

   date     open   high   low   close   stock_id
2013-01-02   10     20     5     15        1
2013-01-02   150    200   100    170       2
2013-01-03   15     30     10    20        1

表2

   date     p_high  p_low   percent   stock_id
2013-01-02   25     10       0.00        1
2013-01-02   210    120      0.00        2
2013-01-03   40     20       0.00        1

我想用这个公式计算百分比

(p_high - high) / high

stock_id = 1 在 date = 2013-01-02 中的百分比将是这样的。

(25 - 20) / 20 = 25.00

当我得到百分比时，我想将它更新到表 2 所以它会像这样，

  date      p_high   p_low   percent   stock_id
2013-01-02    25      10      25.00       1

我怎样才能在 mysql 中做到这一点？

感谢您的帮助。

Answer 1

DDL

create table table1(
    created_dt date,
    open decimal(5,2),
    high decimal(5,2),
    low decimal(5,2),
    close decimal(5,2),
    stock_id integer
);

create table table2(
    created_dt date,
    p_high decimal(5,2),
    p_low decimal(5,2),
    percent decimal(5,2),
    stock_id integer
);

DML

insert into table1 values ('2013-01-02',10,20,5,15,1);
insert into table1 values ('2013-01-02',150,200,100,170,2);
insert into table1 values ('2013-01-03',15,30,10,20,1);

insert into table2 values('2013-01-02',25,10,0.00,1);
insert into table2 values('2013-01-02',210,120,0.00,2);
insert into table2 values('2013-01-02',40,20,0.00,1);

update table2
join table1
on table1.created_dt = table2.created_dt and table1.stock_id = table2.stock_id
set percent = ((p_high - high)/high);

工作示例 http://sqlfiddle.com/#!2/21d02/1/0